taylor_benjamin (11/23/2010)
One way to get SQL Server to consider an index it would otherwise exclude in preference to a table scan is to provide criteria for all columns of the index.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.
It's an old school trick, but it still works.
Aiy yai yai...no one should ever do this, for a couple of different reasons. If the QO isn't choosing your index, it usually has a very good reason why not. In the example you gave, for instance, you're still going to wind up scanning all the rows, index or no. If the QO is choosing a table scan over an index scan, the table scan is probably going to be faster.
Furthermore, in the rare case where the QO isn't selecting the optimal index, you don't want to use a "trick" like this. You just use a WITH index hint to instruct the QO which index it should be using.