PrismitaLEARN · PRACTICE · GROW

What does 'sargable' mean, and why does it matter?

SQL Server Query Tuning Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

A predicate is sargable (Search ARGument-able) if SQL Server can use an index seek for it. Wrapping the column in a function (WHERE YEAR(OrderDate) = 2024), a leading-wildcard LIKE '%x', or an implicit type conversion all make it non-sargable, forcing a scan.

Sargable means a condition is written so the engine can seek an index for it rather than scanning the table. The classic mistake is putting a function on the column — WHERE YEAR(OrderDate) = 2024 — because now every row has to be computed before it can be tested, so the index on OrderDate is useless. You rewrite it as a range: OrderDate >= '2024-01-01' AND < '2025-01-01'. Same problem with implicit conversions, leading-wildcard LIKE, and arithmetic on the column. The rule is keep the column bare on one side and do any manipulation to the constant.

  • Non-sargable: WHERE YEAR(OrderDate) = 2024 → sargable: WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'.
  • Non-sargable: WHERE CONVERT(varchar, Id) = '42' or comparing nvarchar to a varchar column.
  • Non-sargable: WHERE Col + 0 = 10, WHERE ISNULL(Col, 0) = 0.
  • Fix by moving the transformation to the literal side, using a range, or a persisted computed column + index.
All SQL Server questions More on Query Tuning