Databases Reference
In-Depth Information
F
>=
F
<
F
<=
F
BETWEEN
F
LIKE
(only those
LIKE
conditions that have a wildcard character as a suffix, for
example,
FirstName LIKE 'R%'
)
Non-sargable operators:
F
!=
F
!<
F
!>
F
<>
F
NOT EXISTS
F
IN
F
NOT IN
F
LIKE
(
LIKE
conditions that have a wildcard character as a prefix in the filter, for
example,
FirstName LIKE '%R'
)
F
NOT
LIKE
F
Functions on column name in predicate
The main intention for giving a list of operators for both situations is to help you, for as long as
possible, try to avoid non-sargable operators to gain performance benefits from an index.
How to do it...
Follow the steps given here to perform this recipe:
1.
Set two
STATISTICS
options to display information about the query execution:
SET STATISTICS IO ON
SET STATISTICS TIME ON
2.
Now, execute a simple
SELECT
query on
Sales.SalesOrderHeader
with the
IN
operator, which is non-sargable:
SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN (75000,75001,75002)
3.
The statistics received from the Message tab of the result panel is as follows:
Table 'SalesOrderHeader'. Scan count 3, logical reads 9, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Search WWH ::
Custom Search