Now consider these two similar queries:
SELECT * FROM NewOrders
WHERE OrderID ='10249'
SELECT * FROM NewOrders
WHERE OrderID = substring( '10249', 1,5)
Each of these queries contains a SARG and returns the same results. However, although the optimizer can't use the statistics for the index on the OrderID column for the second query, it can use the density information because this query contains an equality. The density information tells the optimizer that each value occurs no more than once, so even though the optimizer doesn't know what specific value it will be looking for, it knows that the query will return no more than one row and that using the index on OrderID is a good choice.
Give the Optimizer a Hand
In an ideal world, the query optimizer would have access to all the information it needed, but right now, that isn't the case. Therefore, the more information you can give the optimizer to help keep it from having to guess, the better plan it can devise. Make your data types explicit so that SQL Server doesn't assume you mean a different type and perform an internal conversion. And make sure you provide data in the most explicit form possible; don't make SQL Server and the optimizer determine function results during query processing. Next month, to help you improve your queries, I'll show you a few more queries that contain SARGs but for which the optimizer has to guess and can't take full advantage of the statistics.