View Single Post
  #21  
Old September 26th, 2005, 12:32 PM
BruceM
external usenet poster
 
Posts: n/a
Default

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.