Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
No there shouldn't be, this recent question claims there is but I've never seen anything like that and am somewhat sceptical... –  Martin Smith Jun 30 '12 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 Jul 21 '12 at 6:36
add comment

2 Answers

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

share|improve this answer
    
yes sorry for that bad thing :/ –  user9399 Jul 2 '12 at 8:27
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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