I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes, etc. However, I was wondering how SQL Server handles multi column indexes (with overlapping column).
Examples:
NONCLUSTERED INDEX Index1 (A, B, C)
NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 and Index2 is not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed without impacting performance?
As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?