Primary key vs. unique index: performance difference with foreign keys?

Question

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

Answer 1

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).

Note:

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.

Sources:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/290619be-b892-4f52-99e0-d8e2ff80aec4

http://bytes.com/topic/sql-server/answers/83999-unique-constraint-vs-unique-index-ms-sql-2000-a

Answer 2

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

Indexes