Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?

share|improve this question

2 Answers 2

up vote 12 down vote accepted

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.

Oh, and the fact that a table can have one PK and many UK :-).

These are both differences in INTENT not in PERFORMANCE. Otherwise, I don't think there's any difference. Behind any PK or UK the SQL Server builds an index (depending on the request, clustered or not) and the way it's used is transparent for the source is coming from.

share|improve this answer
    
So there's no reason to use a PK, except (A) as an alias for NOT NULL UNIQUE CLUSTERED or (B) to highlight that a particular UQ is "special" in a metadata sense, even though the RDMS is agnostic? –  Jon of All Trades Feb 2 '12 at 21:43
    
Yes, something like that, I'd go for option B myself :-). –  Marian Feb 2 '12 at 22:14
    
There is a reason - to stop you using the special value NULL where it is inappropriate! –  JamesRyan Jan 10 '13 at 16:09

Between a clustered primary key and a unique clustered index there isn't any different other than the unique clustered index can have a NULL value.

A non-unique clustered index has a uniqueifier that has to be dealt with for non-unique values.

share|improve this answer
    
Do you mean that further indexes on that table point directly to the rows in the clustered index ignoring the index created by the primary key? –  bernd_k Mar 7 '11 at 14:23
1  
That is correct. Non-Clustered indexes will point to the clustering key, not the primary key. If the primary key is a non-clustered key then all it does is enforce uniqueness on a column which doesn't support NULL values so that it can be used via a foreign key. –  mrdenny Mar 7 '11 at 18:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.