Paul White (6/28/2009)
Grant Fritchey (6/28/2009)
Every time I ran the query, it picked the widest set of includes, regardless of the order in which the indexes were created. I didn't check reads or timings.I ran some tests on 2005 and 2008 with AdventureWorks and about the only constant was that if an exactly-covering index existed, it was chosen. (These were additional indexes I created - not the shipped ones).
Other than that, even with twenty indexes to choose from, with varying numbers of INCLUDEd columns, in different orders, no clear pattern emerged. I thought for a moment that indexes with the required column *last* in the creation order were preferred; but no.
Fascinating question, but I ran out of time with it.
Paul
Huh. Odd. I did the same thing. I had about five different indexes, all with the same key, but varying include columns, but duplicated, like the OP. Every single time, it chose the index with the most columns. Couldn't tell you why. I just didn't dig much past that yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning