Database Reference
In-Depth Information
IN
condition:
•
With the
Table 'SalesOrderDetail'. Scan count 4, logical reads 18
CPU time = 0 ms, elapsed time = 102 ms.
•
With the
BETWEEN
condition:
Table 'SalesOrderDetail'. Scan count
1,
logical reads 6
CPU time = 0 ms, elapsed time = 63 ms.
Replacing the search condition
IN
with
BETWEEN
decreases the number of logical reads for this query from 18 to 6.
As just shown, although all three queries use a clustered index seek on
OrderID
, the optimizer locates the range of
rows much faster with the
BETWEEN
clause than with the
IN
clause. The same thing happens when you look at the
BETWEEN
condition and the
OR
clause. Therefore, if there is a choice between using
IN/OR
and the
BETWEEN
search
condition, always choose the
BETWEEN
condition because it is generally much more efficient than the
IN/OR
condition.
In fact, you should go one step further and use the combination of
>=
and
<=
instead of the
BETWEEN
clause only
because you're making the optimizer do a little less work.
Also worth noting is that this query violates the earlier suggestion to return only a limited set of columns rather
than using
SELECT *
.
Not every
WHERE
clause that uses exclusion search conditions prevents the optimizer from using the index on the
column referred to in the search condition. In many cases, the SQL Server 2014 optimizer does a wonderful job of
converting the exclusion search condition to a sargable search condition. To understand this, consider the following
two search conditions, which I discuss in the sections that follow:
•
The
LIKE
condition
•
The
!<
condition vs. the
>=
condition
LIKE Condition
While using the
LIKE
search condition, try to use one or more leading characters in the
WHERE
clause if possible. Using
leading characters in the
LIKE
clause allows the optimizer to convert the
LIKE
condition to an index-friendly search
condition. The greater the number of leading characters in the
LIKE
condition, the better the optimizer is able to
determine an effective index. Be aware that using a wildcard character as the leading character in the
LIKE
condition
prevents
the optimizer from performing a
SEEK
(or a narrow-range scan) on the index; it relies on scanning the
complete table instead.
To understand this ability of the SQL Server 2014 optimizer, consider the following
SELECT
statement that uses
the
LIKE
condition with a leading character:
SELECT c.CurrencyCode
FROM Sales.Currency AS c
WHERE c.[Name] LIKE 'Ice%';
The SQL Server 2012 optimizer does this conversion automatically, as shown in Figure
18-5
.
