Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 15:01

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Yeah the two different sort orders are required to cover the respective queries. –  CraigN May 12 '11 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.

share|improve this answer

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.