Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:

  • A primary key
  • A unique index
  • 2
    No there shouldn't be, this recent question claims there is but I've never seen anything like that and am somewhat sceptical... Commented Jun 30, 2012 at 20:30
  • There could be if you have not specified both of them either clustered or nonclustered. If both are clustered or nonclustered then there shouldnt be any diff.
    – Gulli Meel
    Commented Jul 21, 2012 at 6:36

2 Answers 2


It has nothing to do with performance as such, but it is a conceptual issue.

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an index which happens to be unique, for instance because you add the primary key to it.

As per the Scenario:

Primary Key

  • When you want to keep a identifier for each row. So each row can be obtianed by the Key value.(since pk unique and not null)

Unique Key

  • When you want to keep secondary identifier where already the primary exists in the same table. So each row can be obtained by your PK or UK. Unique will allow null but only once(what is the use to allow single null).


Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of index you want to use for these keys.




  • yes sorry for that bad thing :/
    – user9399
    Commented Jul 2, 2012 at 8:27

I'm not sure your question has enough information to give a good answer. Whether the PK/UI is Clustered or Non-Clustered is often the dominant performance factor when the resultant join includes fields that were not part of the indexes.

There can be only one clustered index on a table at a time. By default, the PK is clustered. However if a clustered index existed before the PK was created then the PK will be nonclustered (unusual).

So in most cases, since a UI would be created after a PK, the UI would be non-clustered and thus slower when the join results needs to include non-index fields from the row. Comparably, if a clustered UI is created instead of a PK then the performance is expected to be equivalent to a clustered PK.

It is also possible to create indexes with "included" columns. This gets around the clustering issue because it allows the join result to use the included columns without having to physically fetch the row (since the row is in a different location when non-clustered).

Clustered Index


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.