Brandie Tarvin (2/8/2013)
Lynn Pettis (2/8/2013)
Roger Sabin (2/8/2013)
I understand what's been said.But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account.
Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?
You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.
Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?
Think of it, in this case, as a covering index since all columns not part of the index are also included by definition. Yes, if Name had been a part of the nonclustered index, perhaps as suggested an included column, then SQL Server would have used that index instead. It is much smalleer than the clustered index (fewer columns, more "data" per page, etc).