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'
Aside from what Lynn is saying, I recently learned that indexes are also chosen based on column order. I do not know why the first index (that has AltKey) is being chosen for the first query, but SQL Server is correctly choosing the second index on the second query because the order of your SELECT follows the order of columns defined in the second index. That's part of how it knows which indexes it can use.
Food for thought. EDIT: Lynn, feel free to correct me if I'm wrong. I'm still figuring out the innards of indexing.