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.
Look at your execution plans, SQL Server performed an INDEX SEEK on both of them to satisify the respective queries. SQL Server did not have to scan the clustered index to find the data to satisify the query that also returned the value from the CompanyName column (if I remembered the column correctly).