Database Reference
In-Depth Information
Figure 18-5.
Execution plan showing automatic conversion of a
LIKE
clause with a trailing % sign to an indexable
search condition
As you can see, the optimizer automatically converts the
LIKE
condition to an equivalent pair of
>=
and
<
conditions. You can therefore rewrite this
SELECT
statement to replace the
LIKE
condition with an indexable search
condition as follows:
SELECT c.CurrencyCode
FROM Sales.Currency AS c
WHERE c.[Name] >=N'Ice'
AND c.[Name] < N'IcF';
Note that, in both cases, the number of logical reads, the execution time for the query with the
LIKE
condition,
and the manually converted sargable search condition are all the same. Thus, if you include leading characters in the
LIKE
clause, the SQL Server 2014 optimizer optimizes the search condition to allow the use of indexes on the column.
!< Condition vs. >=Condition
Even though both the
!<
and
>=
search conditions retrieve the same result set, they may perform different operations
internally. The
>=
comparison operator allows the optimizer to use an index on the column referred to in the search
argument because the
=
part of the operator allows the optimizer to seek to a starting point in the index and access all
the index rows from there onward. On the other hand, the
!<
operator doesn't have an
=
element and needs to access
the column value for every row.
Or does it? As explained in Chapter 14, the SQL Server optimizer performs syntax-based optimization, before
executing a query, to improve performance. This allows SQL Server to take care of the performance concern with
the
!<
operator by converting it to
>=
, as shown in the execution plan in Figure
18-6
for the two following
SELECT
statements:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE poh.PurchaseOrderID >=2975;
SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE poh.PurchaseOrderID !< 2975;
