taylor_benjamin (11/23/2010)
In the case of your article, the optimizer was intelligent enough to know that the existing index was more optimized than a table scan. When this doesn't work, simply specify not restricting criteria in place of missing attributes.With your example, included LastName > 'A' in your where clause will meet the qualifications for the index.
Maybe, but go and look at what's actually happened in that case.
With the leading column searched on an inequality, SQL can't seek on both columns, only on the first. So it seeks on the first, and finds all the rows. Great, you've got yourself an index seek that is in essence an index scan (read of all the pages in an index). Looks good in the execution plan if you don't know what you're looking for, but it's no more efficient than the query would have been had you omitted that predicate.
http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/
If SQL went for a table/clustered index scan over the index scan, it means the index is not covering and it thinks that the key lookups will be more expensive than a clustered index scan. A non-filtering predicate is unlikely to change its cost estimates. From the statistics it will be able to tell that LastName > 'A' has a selectivity of 1 (all rows qualify)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability