LLI Wiki — My Digital Knowledge Base

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

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.

Mastodon LLIMastodon SocialFosstodon