How Can I Improve Performance of Range Search Queries?

Here is a nice little trick that can make a great big difference in query performance. I

Sometimes SQL Server fails to use an existing table index when processing a query that does a range search. For example, if the search criteria in a query is like one of the following and ColData has a non-clustered index, SQL server may not use the index.

Where ColData Between 'Z21' and 'Z47'

Where ColData>='Z21' And ColData<='Z47'

It is possible to force SQL Server to use the index by adding an optimizer hint to the query (for example, Index=IndxColData). We can also cause SQL Server to use the index by adding additional criteria to the query as shown below.

Where ColData Between 'Z21' and 'Z47'
And ColData=ColData

Why add the criteria ColData=ColData? The idea here is to trick the query optimizer into using the correct index. The query optimizer almost always chooses to use an existing index for an equality search whereas it may not choose the index for a range search on the same column.

Note that the second condition is always true regardless of the records chosen. In fact, the Query Optimizer recognizes that the condition is always true and doesn't include it in the final query execution plan. :