Roger Sabin (2/8/2013)
So, you are saying that SQL Server determined --the cost of finding a few records (using second index) + lookup for other fields
was greater than ---
the cost of searching through thousands of records (using first index) to find the few matching that where clause but without a need to do a lookup for the additional fields needed
and that is why the first index was chosen.
I always thought that the index with the greatest number of equality matches (in the order of the columns in the index without any gaps) was always selected. Only when there were range matches or two or more indexes could satisfy the equality matches would it take into account things like lookups.
It didn't search through thousands of records, the clustered index was selective enough that it performed a Clustered Index Seek, not a Clustered Index Scan and found the records and didn't then have to perform a second Clustered index seek and then combine the results in order to get the full data. If you want it to use the non-clustered index, make the non-clustered index include the columns, otherwise the clustered index seek operation is more efficient.