>Hello!
>
>In my quest for SQL perfection, I've stumbled across an interesting observation. If someone can help me understand why this is so, I may be able to arrive at a solution to my need:
>
>I have a SQL Server database with a table with 1.5 million records. The table has about 30 fields, including LName and ID, both with indexes.
>On a client machine, I run this T-SQL statement:
>
>SELECT ID FROM MyTable WHERE LName LIKE 'SM%'
>
>This returns about 12,000 records in 45 seconds.
>
>If I run this T-SQL statement:
>
>SELECT LName FROM MyTable WHERE LName LIKE 'SM%'
>
>This returns about 12,000 records in only 2 seconds! Very big difference.
>
>I've rebooted before each statement, so it's not a caching thing.
>Why is returning the field being queried so fast compared to returning a corresponding ID field? At first I thought maybe because the ID field has a unique value for every record, whereas the queried fields are all the same. But in using the "LIKE" operator, I get 98 unique values (SMITH, SMALL, SMILEY, etc...) within the 12,000 records. Maybe there's somthing with that. Any ideas?
>TIA!
>Mark

Well... it would seem to me that to return ID where Lname SQL has to read TWO fields to build the result set, but to return Lname where Lname it only has to read 1 field... but, I can't imagine such a difference.

Also, when you say, you rebooted the machine, did you reboot the server. If not, all of that info was in the cache... Try running the queries in the other order.

BOb