OK, I was blind and not thinking (no need to respond Rox :) )

SELECT ID FROM MyTable WHERE LName LIKE 'SM%'

SELECT LName FROM MyTable WHERE LName LIKE 'SM%'

The second query is 'covered' by the LNAME index. A covered query can be solved without hitting the table because all the necessary columns are within the index. In this case, SQL Server will traverse the index looking for the first index key that matches SM. It will then scan the leaf level WHILE the key continues to match SM. Since all keys are stored in ascending order at the leaf level, SQL Server can stop scanning the leaf level of the index when it encounters the first key value that does not match SM.

To solve the first query, SQL Server will again traverse the index as before but because the values we want is not in the index, SQL Server will have to access the table. Attached to the index key is a pointer called a RID or Row Identifier. If the table does not have a clustered index, the RID will point to the physical location of the row in the form of File#:Page#:Slot#. For every matching key in the LNAME index, SQL Server will have to use the RID to find the page in the table, and pull the ID column.

Creating a composite index on ID, LNAME should give you good performance because it covers both queries.

-Mike