Roger Sabin (2/7/2013)

---

The two indexes are as follows:

Name: IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber
Indexed Columns: YearNum, MonthNum, AltKey and AccountNumber

Name: IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber
Indexed Columns: YearNum, MonthNum, AccountNumber


If we use the following select statement, the second index (IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber) is correctly used:

select YearNum, MonthNum, AccountNumber from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'


But, if we use the following select statement, the first index (IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber) is incorrectly used:

select YearNum, MonthNum, AccountNumber, Name from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'

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?

Lynn Pettis (2/8/2013)

---

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.