INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server Performance Issues

How Can I Improve Performance of Range Search Queries? by tlbroadbent
Posted: 28 May 01


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

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.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close