Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to: Manage multiple overlapping indexes in SQL Server 2005

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)?

like image 896
Nick Vaccaro Avatar asked Aug 12 '10 16:08

Nick Vaccaro


2 Answers

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

like image 186
a1ex07 Avatar answered Sep 21 '22 18:09

a1ex07


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.

like image 45
Martin Smith Avatar answered Sep 22 '22 18:09

Martin Smith