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.