In SQL Server, suppose I have a very large table (columns A through Z) which has a CLUSTERED PRIMARY KEY on A and a UNIQUE NONCLUSTERED index IX1 over columns B,C,D include E,F,G. I then need to cover another query with a new NONCLUSTERED index IX2 over D,C,B include J,K,M.

With IX1 already enforcing uniqueness across B,C,D does it make sense to create IX2 UNIQUE as well? Are there any serious pros and cons.

  • does the second one need to be unique across d, c, and b? i would try just creating the index on the three and include the other three columns if it doesn't.
    – Thirster42
    May 12, 2011 at 15:01

2 Answers 2


If your index is unique, then tell SQL it's unique. The optimiser can use the additional info, there's no downside to making it such.

That said, do you really need a second index? Is the different order of the keys for some other queries to use? If not, why not just add the additional include columns to the first index, it'll likely be cheaper in the long run.

  • Yeah the two different sort orders are required to cover the respective queries.
    – CraigN
    May 12, 2011 at 15:34

I'm not sure how the specific scenario would play out but you should be able to test it out without too much trouble.

In the dev environment (if it exists) try running queries with both scenarios and check out the execution plans. If the second index is preventing things like table scans then there's a good chance that it's worth it. If you find that the first index is really doing all the work then it's probably not.

I don't see any major cons outside of the usual stuff when it comes to indexes. If you start adding too many your inserts, updates, and deletes can begin to slow down and it also takes up more disk space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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