0

I have 2 nonclustered indexes:

IX1 = [col1]  
IX2 = [col1],[col2] (unique).

I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied the query in question, so should not hurt performance. But I had a nagging thought in my head, it might not be able to use the 2nd index if it was marked as unique?

Both columns are integers datatype, col1 is a FK

  • 1
    Unique will not preclude using the index. – Dan Guzman Dec 2 '19 at 11:20
  • @DanGuzman would that also apply if the longer index was a clustered index? – DamagedGoods Dec 2 '19 at 12:29
  • Clustered index is having totally different concept, you may ask another question with appropriate details and someone will answer that. – Learning_DBAdmin Dec 2 '19 at 12:52
  • its the same question. but instead of being a unique index, would that apply to using part of a clustered index. I know that clustered index represents the physical ordering of a table vs a non clustered index. But can you use part of that clustered index? – DamagedGoods Dec 2 '19 at 14:50
  • Ideally clustered index should have minimal no. of columns because this will also be part of all NC indexes(hidden columns). brentozar.com/archive/2013/07/… Unique index is still part of NC index. Hope this clarifies. – Learning_DBAdmin Dec 2 '19 at 15:29
2

Yes, it should be safe to remove the IX1 as IX2 is already covering col1. If IX2 was defined in reverse order i.e. col2,col1 then scenario would be a bit different as first column in the index is of utmost importance. Moreover IX2 is a unique constraint which means combination of col1 and col2 will be unique in the table so, you should be careful for dropping or modifying this kind of index.

Indexes help in select however slows down inserts/updated and deletes. If you have multiple indexes with same set of column that means you have that many copy of those columns and will slow down any DML operation as optimizer has to modify all the copies of columns in all these places.

Preferred solution for indexes is to have less number of indexes with higher number of columns than more number of indexes with less columns(Ideally speaking however there are many exceptions).

Same is described by SQL Server SME Mr. Brent Ozar as below:

I prefer to drop that first index and let the slightly bigger index pick up the slack. Reading a slightly larger index will take slightly more time: if a query didn’t need that field, it still has to pull it off the disk in order to perform the query. However, dropping the index pays off during inserts/updates/deletes, because it’s one less index SQL Server has to manage. It also makes the database smaller, thereby making database maintenance tasks smaller/faster.

If:

I have two indexes with the exact same fields in the same order, but One has 1-2 extra fields, and There aren’t include fields, or the include fields are the same Then I’ll drop the shorter index with extreme prejudice.

You may check below links also for more details:

https://blog.sqlauthority.com/2015/03/26/sql-server-mistake-to-avoid-duplicate-and-overlapping-indexes-notes-from-the-field-073/

https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1

https://stackoverflow.com/questions/30604471/sql-index-is-this-overlapping

Hope above helps.

  • thanks, I understand the covering indexes.. really just wanted to confirm if using part of a unique key was still viable – DamagedGoods Dec 2 '19 at 14:46
  • Glad to know it helped. – Learning_DBAdmin Dec 2 '19 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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