Can I drop an overlapping index if the larger index is unique 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
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.