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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.