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.