If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#9
|
|||
|
|||
Physical is Disk!
Clustered Index can only be a Primary Key. You may need several fields to define uniqueness, several fields can make up an index and a primary key which is actually an index also as opposed to a field. Order can be anything you want whenever you want it using SQL. If you are going to sort by a specific field or combination of fields you may consider adding an index to that field or combination of fields. Indexes speed things up when sorting and analysing data, they can slow things down if you are inserting data, especially bulk updates. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "BruceM" wrote in message ... Thank you for the explanation. It makes sense that it has to do with physical ordering in a table rather than on the disk. Having said that, I cannot discover the connection between indexes, the table's Order By property, and anything else that suggests an order within the table, on the actual order of records in the table. Order By, in particular, seems to accomplish nothing. Regarding John Doe, it may well be a name used by more than one person. How does this fit in with clustered indexes? I may need duplication in that field. Suppose I wanted to create a clustered index in an Access table. How would I do that? The term does not appear in Access Help, and discussions of the subject tend to assume the reader knows what a clustered index is and how to create one. Even if one is created, what benefits will I notice? "Craig Alexander Morrison" wrote in message ... Jet 4.0 and 3.5 (and earlier versions) cluster on the Primary Key and a Compact will keep it managed. Indeed a clue to this is the Registry entry for: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engi nes. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes both contain the setting CompactByPKey. I am not sure what would happen if you changed the above setting from 1, I expect 0 would skip the clustering - I am not sure if any other setting would be valid. SQL Server generally clusters on the Primary Key, however, you can select another index. AutoNumbers are very poor devices to truly define a unique record in the real world, You can enter the name John Doe 1,000,000 times in your database if the Primary Key is an AutoNumber and you have failed to do something to prevent the creation of 1,000,000 John Doe's. You may have 1,000,000 unique records but so what? Recommending the AutoNumber as Primary Key without pointing out the dangers, and suggesting the definition and declaration of the natural key (should one exist), is unwise. BTW A clustered index is merely a physical ordering of the records in a table in the database file. Using the true natural key (should one exist) as the primary key will ensure that all the records with a similar PK will be physically located next to each other. Using an AutoNumber (sequential order) as PK will mean the records are clustered according to their creation order. Using IDENTITY and AutoNumber as PK defeats the purpose of PK, this is not so bad in SQL Server as it allows you to choose something more sensible if you have an IDENTITY field in use as PK. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "BruceM" wrote in message ... That you disagree with somebody does not make that person wrong. Roger has provided a wide range of assistance in this forum, and has made samples available on his web site. Based on his track record I would be inclined to follow his advice. If you are trying to convert people to the idea of using clustered indexes, a very basic discussion of what they are would be most helpful. I have taken your suggestion to look at Google groups. There is indeed a lot of discussion, but I have not yet found how I would create a clustered index if I wanted to. My databases with a few thousand records seem to work just fine. Why would I want to put extra effort into something that already works well? I know you have posted code that includes MAKE TABLE or some such, but the utility of such code is not clear. The other thing I noted in Google groups is that most of the discussion of clustered indexes seems to be in discussions about SQL server. wrote in message oups.com... Roger Carlson wrote: Autonumber fields make excellent Primary Keys. You've misunderstood what PRIMARY KEY means. An unique integer which has no meaning in respect fo the entities being modelled makes a lousy PRIMARY KEY. Google for "clustered index" in the Access groups. An autonumber is a convenient uniqueifier but unquieness for its own sake make not be such a good thing. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 3 | March 6th, 2005 08:41 PM |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 0 | March 6th, 2005 01:33 AM |
Additional fields for form based parameter query/null fields | geeksdoitbetter | Running & Setting Up Queries | 2 | January 7th, 2005 10:05 PM |
Selecting Fields for Update | Steve Daigler | Page Layout | 4 | October 15th, 2004 02:13 PM |
My tables lost their AutoNumber fields | Bill Nicholson | Database Design | 2 | July 2nd, 2004 02:20 AM |