Friday, February 4, 2011

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’

No comments:

Post a Comment

All suggestions are welcome