• I think that there are only three points that need to be got over to people on this topic:

    (a) a nonclustered primary key and a non-clustered unique index where the columns are constrained NOT NULL are functionally and performance-wise identical.

    (b) a clustered primary key and a clustered unique index where the colums are constraint NOT NULL are functionally and performance-wise identical.

    (c) when columns in a unique index are not constrained NOT NULL there is a trivial functional difference from the case where they are constrained NOT NULL, but there is still no performance difference from the correstponsing primary key. The functional difference is sometimes useful - sometimes it is useful to allow nulls, sometimes it isn't, somethimes it is usefull to forbid nulls, sometimes it isnt, it depends on whether it is useful to be able to insert things before one knows enough to say exactly what they are or more useful to insist that we know what something is before we insert it; But this trivial difference has no impact whatever on IO performance, or (in fact) on any other aspect of performance.

    I think there are some articles already covering this (I've read some good articles about indexes here; but maybe I wouldn't have noticed if they skipped this extrremely elementary level, so I'm not really certain it's covered). I also think that it's such a basic thing that every DBA or Database developer should be 100% aware of it before they are allowed to even think about schema desig, never mind actually do any. The thing is that I can't envisage an article aimed only at comparing the unique index with a primary key on the same columns being any longer than half a page (and I would probably have trouble making it even that long, despite my regrettable inclination towards pedantic verbosity).

    Tom