Primary key vs. unique index: performance difference with foreign keys? 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
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