How to: Manage multiple overlapping indexes in SQL Server 2005

Question

For the sake of example, I have a table with columns A B C D E F G H.

I have created two indexes on the table that correspond to the most used queries. The first is on columns B C D and E. The second is on B C D E and F.

The queries that use these columns are called the same number of times and they are each optimized with respect to the indexes.

My issue is - due to the multiple indexes I have on this table, the row size is quite large. I'd like to remove one of these indexes, but can't decide which one.

My question is - if I remove the first index (BCDE), will a query that uses these columns still be optimized by an index on (BCDEF)?

Answer 1

Yes. Although it might take a bit more I/O for any scans on the index as the index is a bit wider so will span more pages.

Answer 2

yes, it will. as well as queries that benefit from using an index on (B), or on (BC), or ON (BCD)