In one line: wrap an indexed column in a function, arithmetic or ISNULL(), and SQL Server usually has to compute the expression for every row instead of seeking. Leave the column bare and move the work to the other side of the comparison.
Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/non-sargable-predicates on GitHub.
What you’ll see
- An index on exactly the column you filter, and the plan scans it anyway.
- Reads far out of proportion to the rows returned. In the demo, wrapped predicates read up to 305× more than their rewrites.
- Estimates of 1 row where hundreds or thousands come back, which then drive bad join choices and memory grants further up the plan.
Why it happens
A predicate is SARGable (Search ARGument-able) when the optimizer can turn it into a range on an index. WHERE YEAR(EventTime) = 2024 isn’t a range on EventTime. It’s a value computed from it, so every row has to be read, computed and compared. The statistics histogram describes the column, not the expression, so the estimate is lost as well.
What to do
| instead of | write |
|---|---|
YEAR(col) = @y, CONVERT(char(8), col, 112) = @s, DATEDIFF(day, col, @d) = 0 | col >= @from AND col < @to |
DATEADD(day, 30, col) >= @d | col >= DATEADD(day, -30, @d) |
ISNULL(col, 0) = 0 | (col = 0 OR col IS NULL) |
ISNULL(col, 0) = 25 | col = 25 |
LEFT(Name, 5) = @p | Name LIKE @p + '%' |
UPPER(Name) = @p, LTRIM(RTRIM(Name)) = @p | Name = @p (case-insensitive collations and = already ignore case and trailing spaces) |
Amount + 1 = @v | Amount = @v - 1 |
Can’t change the query? Index a computed column that matches the expression. In the demo, YEAR(EventTime) = @y seeked an index on EventYear AS YEAR(EventTime) without the query mentioning it: 184 reads instead of 1,996.
Check that each rewrite returns the same answer. A rewrite that changes the result is a bug, not an optimization.
What doesn’t help
CAST(col AS date)as a model. It’s the famous exception: it does seek. But it estimated 1 row against 274 actual, and the half-open range got both the seek and the right estimate.- Trusting the operator name.
Name LIKE '%' + @pcompiles to an Index Seek and reads every page of the index, exactly as much as the scan. Check the reads, not the plan icon. A leading wildcard has no SARGable rewrite.
Check your server
-- Costliest cached statements that scan an index. A starting list for looking
-- at their WHERE clauses for functions on columns.
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (20)
qs.execution_count,
AvgReads = qs.total_logical_reads / NULLIF(qs.execution_count, 0),
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') = 1
ORDER BY qs.total_logical_reads DESC;See it happen
The non-sargable-predicates demo runs 21 typed queries against a million-row table. It pairs each wrapped predicate with its bare-column rewrite, checks that both return the same count, and records the operator, the reads and the estimate.
Related: implicit conversions · key lookups and the tipping point · partition elimination
Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.
Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.
