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?


2 Answers 2


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.

  • 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? Commented Feb 2, 2012 at 21:43
  • Yes, something like that, I'd go for option B myself :-).
    – Marian
    Commented Feb 2, 2012 at 22:14
  • There is a reason - to stop you using the special value NULL where it is inappropriate!
    – JamesRyan
    Commented Jan 10, 2013 at 16:09
  • @JamesRyan: That's covered by Jon of All Trades's reason #A.
    – Andriy M
    Commented Apr 29, 2015 at 13:02
  • 1
    @JamesRyan: Yes, between PRIMARY KEY and NOT NULL UNIQUE, the former would be much harder to turn into NULL UNIQUE (particularly if the constraint was already referenced by a foreign key). Would certainly prevent accidental changes from NOT NULL to NULL.
    – Andriy M
    Commented Apr 30, 2015 at 0:14

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.

  • 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
    Commented Mar 7, 2011 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
    Commented Mar 7, 2011 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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