Query Design Analysis - SQL Server Query Performance Tuning - page 350

Avoid Nonsargable Search Conditions
A sargable predicate in a query is one in which an index can be used. The word is a contraction of “Search ARGument
ABLE.” The optimizer's ability to benefit from an index depends on the selectivity of the search condition, which in
turn depends on the selectivity of the column(s) referred to in the WHERE clause, all of which are referred back to the
statistics on the index. The search predicate used on the column(s) in the WHERE clause determines whether an index
operation on the column can be performed.
The sargable search conditions listed in Table 18-1 generally allow the optimizer to use an index on the
column(s) referred to in the WHERE clause. The sargable search conditions generally allow SQL Server to seek to a row
in the index and retrieve the row (or the adjacent range of rows until the search condition remains true).

Table 18-1. Common Sargable and Nonsargable Search Conditions

Type Search Conditions

Inclusion conditions = , > , >= , < , <= , and BETWEEN , and some LIKE conditions such as
LIKE '<literal>%'

Sargable

Exclusion conditions <> , != , !> , !< , NOT EXISTS , NOT IN , and NOT LIKE IN , OR , and some LIKE
conditions such as LIKE '%<literal>'

Nonsargable

On the other hand, the nonsargable search conditions listed in Table 18-1 generally prevent the optimizer from
using an index on the column(s) referred to in the WHERE clause. The exclusion search conditions generally don't allow
SQL Server to perform Index Seek operations as supported by the sargable search conditions. For example, the !=
condition requires scanning all the rows to identify the matching rows.
Try to implement workarounds for these nonsargable search conditions to improve performance. In some cases,
it may be possible to rewrite a query to avoid a nonsargable search condition. For example, consider replacing an
IN/OR search condition with a BETWEEN condition, as described in the following section.

BETWEEN vs. IN/OR
Consider the following query, which uses the search condition IN :

SELECT * FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID IN (51825,51826,51827,51828);

Another way to write the same query is to use the OR command:

SELECT sod.*
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID = 51825 OR
sod.SalesOrderID = 51826 OR
sod.SalesOrderID = 51827 OR
sod.SalesOrderID = 51828 OR

You can replace either of these search condition in this query with a BETWEEN clause as follows:

SELECT sod.*
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID BETWEEN 51825 AND 51828;