Doug,
Quite nice of you to help out a friend.
SQL Server uses a cost based optimizer. When it is evaluating the query, it will determine how useful an index is. It does this by looking at some statistics that describe the distribution of the data within the index. If SQL Server determines that it is more work to use the index than do a table scan, it will do the table scan.
An eash way to see this is for your friend to run the following queries:
SET STATISTICS IO ON
SELECT * FROM appl WHERE name_licensee LIKE 'tab%'
SELECT * FROM appl WITH INDEX(
) WHERE name_licensee LIKE 'tab%'
SET STATISTICS IO OFF
Where is the name of the index on the name_licensee column.
The WITH INDEX() is an optimizer hint. With it, I forcing the optimizer to use the specified index. We're interested in the Logical Page count. If the first query has a smaller value, SQL Server is solving the query in the best possible way. If the second query has a smaller value...
Now, why your friend is seeing this results is a different matter. Maybe the statistics are not to date. SQL Server does maintain the statistics, but only when it determines that enough changes have been made to the table.
I'd also like to suggest a book: Inside SQL Server 7.0 by Delaney.
-Mike
