Mike,
Thanks so much for your help! You certainly went in my direction! I feel kinda stupid now, because I did not realize you can include more than one column in an index. So I set up an index on ID, LName and BAM! FAST!
I was really grinding the gears in this paradigm shift, because in Fox, you only include the fields that you compare in the index, not the ones you retrieve.
Thank you, and Bob, and Mark M., and everyone who has helped! I have hope now for SQL Server (:D)
Mark

>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