Sargable predicates: rewrite filters to use an index
Sargability rules and common SQL Server filter rewrites so that the filters can use an index.
A predicate is sargable (from Search ARGument Able) when the engine can use an index to evaluate it. Otherwise the engine often has to read every row of the table. This article summarises the rules and the common rewrites in SQL Server, based on an article by SQLpro (source link).
General rules
- A predicate is sargable when the column is compared to a value, without transformation, in the sort direction of the index.
- The operators
NOT,<>,ORandINwith several values are generally not sargable. - A column placed inside a calculation or a function is generally not sargable. Watch out for implicit data type conversions too.
- A
LIKEpattern that starts with a wildcard ('%value','%value%') cannot use a regular index.
Some optimizers rewrite these predicates themselves. The table below is therefore a guide, to be checked in the execution plan.
General rewrites
| Non-sargable | Sargable rewrite |
|---|---|
Column OPERATOR value2 = value1 |
Column = value1 OPERATOR value2 |
Column1 = value1 OR Column2 = value2 |
Column1 = value1 UNION [ALL] Column2 = value2 |
NOT Column < value |
Column >= value |
NOT Column BETWEEN value1 AND value2 |
Column < value1 UNION [ALL] Column > value2 |
Column LIKE '%value' |
Search on the reversed column: ennoloC LIKE 'eulav%' |
Column LIKE 'val%ue' |
Column LIKE 'val%' INTERSECT ennoloC LIKE 'eu%' |
Column IN (value1, value2) |
Column = value1 UNION [ALL] Column = value2 |
ennoloC is the reversed column (REVERSE function), stored in a persisted computed column and indexed.
A LIKE '%value%' can be made sargable with a rotated-index technique. Database engines do not implement it natively yet, but it can be simulated in a table.
Special cases in SQL Server
| Non-sargable | Sargable rewrite |
|---|---|
YEAR(COL_DATE) = 2008 |
COL_DATE >= '2008-01-01' AND COL_DATE < '2009-01-01' |
YEAR(COL_DATE) = 2008 AND MONTH(COL_DATE) = 9 |
COL_DATE >= '2008-09-01' AND COL_DATE < '2008-10-01' |
DATEDIFF(yy, COL_DATE, GETDATE()) > 18 |
COL_DATE < DATEFROMPARTS(YEAR(GETDATE()) - 18, 1, 1) |
UPPER(COL_CHAINE) = 'MARTIN' |
COL_CHAINE = 'MARTIN' COLLATE French_CI_AS |
COL_CHAINE = 75001 |
COL_CHAINE = '75001' |
COL_NOMBRE = '1999' |
COL_NOMBRE = 1999 |
The UPPER rewrite assumes a case-insensitive collation, here French_CI_AS. In the last two cases the literal matches the column data type: otherwise SQL Server converts the column, which prevents the use of the index.