>SQL Server: Mind Your Search Conditions In WHERE Clause


>

An index operation on the columns(s) in WHERE clause, can be performed or not, depends upon your search conditions.
Rule is simple. Exclusion searches generally prevent the optimizer from using an index on the columns, referred to in the WHERE clause. While Inclusion search conditions are also helpful for SQL Server to perform index seek operations.
Though every time it is not possible, still when ever it is possible, try to avoid using exclusion search conditions.
Here is a list of both exclusion and inclusion search conditions.
Inclusion Search Conditions
Exclusion Search Conditions
=
<>
>
!=
>=
!>
<
!<
<=
NOT IN
BETWEEN
NOT LIKE IN
LIKE operator with literal i.e. LIKE ‘literal%’
LIKE operator with  % sign first i.e. LIKE ‘%literal’
Advertisements

Posted on February 4, 2011, in Index, Performance Tuning, Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: