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 |
#26
|
|||
|
|||
Thanks for taking the time to reply. In particular, I now understand that
making a field (or combination of fields) the PK creates what you have been referring to as a clustered index. I am going to combine things from several of your replies here. If I use name and address as the PK, what becomes of relationships if the name and/or address changes? I would want my PK to be something that will not change. Also, having two people with the same name at the same address is not at all unusual. For women in particular the designation Jr., III, etc. is rarely used, so the names are literally indistinguishable without further clarification. Birth date is one way of distinguishing the two, but it may not be appropriate to gather such information. It may be that a comments field or something of the sort is the only way of telling the two apart. A mother and daughter under the same roof, whether at a family business or at home, is far from an unlikely occurence, and one against which I must guard. I use queries (SQL) for ordering. In a Contacts table or some such listing of names I generally use name, for instance. I base forms and reports on the query, not on the table. How does this enter into ordering of data as you have described it? wrote in message oups.com... BruceM wrote: Suppose I wanted to create a clustered index in an Access table. How would I do that? That's it! You've hit on the golden question. You create a clustered index by using the PRIMARY KEY declaration. There is no other way to create a clustered index in Access/Jet. If you want a non-nullable unique CONSTRAINT, you use NOT NULL UNIQUE. If you want a non-nullable unique clustered INDEX, you use PRIMARY KEY. CONSTRAINTs are all about data integrity (logical). INDEXes are all about performance (physical). 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. There is info out there but it is easy to miss. One view is that there is no 'choice' for a table's clustered index, it's either PK order (PK exists) or data/time order (no PK exists). In SQL Server, for example, you can explicitly specify NONCLUSTERED. In Access/Jet, CLUSTERED is implicit, default and compulsory i.e. comes as standard with PK every time even if you don't want it. The point is, for an autonumber you *don't* want it. Here's a couple of relevant articles you may have missed: ACC2000: Defragment and Compact Database to Improve Performance http://support.microsoft.com/default...b;en-us;209769 New Features in Microsoft Jet Version 3.0 http://support.microsoft.com/default...b;en-us;137039 Even if one is created, what benefits will I notice? What are the benefits? Improved performance, especially with queries that can take advantage of physically contiguous rows e.g. GROUP BY or BETWEEN constructs. That is assuming you've chosen the PK appropriately. Conversely, if you've chosen unwisely, e.g. you've made you autonumber column the PK, you will take a performance hit. Will you notice? There are too many factors to generalize; you must test. With a table of 100 rows, I doubt you would be able to *measure* any performance difference 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. I suppose an autonumber could help you out here i.e. you only need (last_name, first_name) for you clustered index but you need to satisfy the UNIQUE attribute that PRIMARY KEY requires. Note the ordinal position of the columns in the PRIMARY KEY declaration are significant CREATE TABLE Blah ( first_name VARCHAR(35) DEFAULT '{{NK}}' NOT NULL, last_name VARCHAR(35) DEFAULT '{{NK}}' NOT NULL, ... (other columns) ... uniquifier IDENTITY (1,1) NOT NULL, PRIMARY KEY (last_name, first_name, uniquifier) ); ... However, the autonumber is usually not required because there should be a natural key i.e. attribute(s) which uniquely identify an entity. So use the existing key at the end of the PK declaration. Using an autonumber in place of (rather than in addition to) a natural key will lead to pain sooner or later. |
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 09:41 PM |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 0 | March 6th, 2005 02:33 AM |
Additional fields for form based parameter query/null fields | geeksdoitbetter | Running & Setting Up Queries | 2 | January 7th, 2005 11: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 |