Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
