SQL Server Unique Index and Querying?

Question

I have a simple table with two columns, an int identity primary key and a varchar(64). I am putting a unique constraint/index on the VarChar column to enforce uniqueness. The regular query pattern is to query the column for a value, and then use the PK value in a join to find other data in other tables.

Is there any speed benefit to making the nonclustered index unique?

Does it make sense to include the PK column in the nonclustered index, or is it already there because it is the clustered PK in the first place?

Thanks.

Yes - it makes sense to make the non-clustered index unique. And assuming your primary key is clustered - then there's no need to explicitly include it in the non-clustered index - the clustering key is part of every non-clustered index already anyway - always. — marc_s
– marc_s, Commented Jun 7, 2012 at 21:13

RMD · Accepted Answer · 2012-06-07 21:00:37Z

2

You don't need to add the PK to any non-clustered indexes. As you point out, all non-clustered indexes already include the PK.

And yes, having a unique constraint will increase performance when quering that index because the query optimizer can use different mechanisms to find your data if it knows there will only ever be a single match.

answered Jun 7, 2012 at 21:00

RMD

3,2438 gold badges46 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zhenny Over a year ago

Also, having a unique constraint will always save you space. SQL has to be able to uniquely identify each row. The space savings is fairly minor though. sqlpassion.at/blog/…

marc_s Over a year ago

A non-clustered index includes the clustering key - not the primary key. Yes - by default and most of the time, that's the same thing - but just be clear - it's the clustering key that's part of every non-clustered index (and that could be different from the PK - in rare cases)

Snowy Over a year ago

marc_s yes you are correct, I should have specified that in my question. Thanks.

Collectives™ on Stack Overflow

SQL Server Unique Index and Querying?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related