4

Using Sql Server 2014, I have several tables where the PK field is an int, but I also have a UniqueIdentifier field that I need to query by. This field will always be unique.

I am adding indexes for these and I have the option of choosing Index or Unique Key.

Is there any benefit to choosing one or the other?

Improve this question

3 Answers 3

Reset to default
8

Unique keys are indexes. If your values are guaranteed to be unique, this is the best choice.

Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Improve this answer
5

A few things here that have not already been mentioned.

First, is that a UNIQUE constraint can also be used for Referential Integrity. Per the BOL article on Unique Constraints and Check Constraints:

A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

This may be helpful with your database design depending on how complex you wish to get, but more importantly as Grant Fritchey explains, it can also help improve performance for certain types of queries if you do create a FOREIGN KEY constraint on it.

Secondly, and this is probably the more important fact, a UNIQUE constraint will likely be a smaller index to it's Non-UNIQUE equivalent, even if the Index Definitions are exactly the same. When a Nonclustered Index is defined as UNIQUE, the clustered key is implicitly stored as (an) INCLUDE column(s) (because nonclustered indexes have to point back to the clustered key). Alternatively, if the Nonclustered index is NOT defined as UNIQUE, the clustered key is implicitly appended to the Nonclustered Index Key and will exist at all levels of the index (making it bigger). Kalen Delaney does a much better job of explaining why this occurs in her posts on Nonclustered Index Keys and More About Nonclustered Index Keys.

Improve this answer
1
  • Thanks John. I had no idea the clustered key was held differently depending on uniqueness. I'd assumed it was always implicitly an Include.
    – PseudoToad
    Commented Aug 29, 2017 at 2:48
0

Both are essentially the same in some ways. An index does not have to be unique. A Primary Key does and a PK identifies a record. A unique identifier really applies more to a column not having identical values. Depending on what you are doing with it should tell you, but you may need both.

Improve this answer

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.