CODEWITHSUNDEEP
sqlsql-serversql-server-2005indexing
Nick Vaccaro
Nick Vaccaro

Reputation: 5504

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

Upvotes: 5

Views: 571

Answers (2)

a1ex07
a1ex07

Reputation: 37382

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

Upvotes: 7

Martin Smith
Martin Smith

Reputation: 454020

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.

Upvotes: 3

Related Questions