SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

LingamSuyambu — Thu, 29 Oct 2026 18:02:01 +0000

Originally published at SQL & Cloud Lab.

You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite.

What SARGable means

SARGable stands for Search ARGument-able. A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan.

The rule of thumb: leave the column bare on one side of the comparison, and do any calculation on the other side.

Common non-SARGable patterns and their fixes

Functions on date columns

-- Not SARGable: YEAR() runs on every row
WHERE YEAR(OrderDate) = 2026

-- SARGable: a range on the bare column
WHERE OrderDate >= '2026-01-01'
  AND OrderDate <  '2027-01-01'

Use a half-open range (>= start and < next start) so it works correctly for date and datetime columns alike.

String functions

-- Not SARGable
WHERE LEFT(EmployeeCode, 3) = 'ABC'
WHERE SUBSTRING(EmployeeCode, 1, 3) = 'ABC'

-- SARGable: a prefix LIKE can seek
WHERE EmployeeCode LIKE 'ABC%'

Note that a leading wildcard, such as LIKE '%ABC', cannot seek, because the index is sorted by the start of the value.

Arithmetic on the column

-- Not SARGable
WHERE Amount * 1.18 > 1000

-- SARGable: move the arithmetic to the constant
WHERE Amount > 1000 / 1.18

ISNULL and COALESCE in filters

-- Not SARGable
WHERE ISNULL(Status, '') = 'Open'

-- SARGable: NULL never equals 'Open', so the function is not needed
WHERE Status = 'Open'

-- When you really need NULLs too
WHERE (Status = @Status OR Status IS NULL)

Implicit conversions

This one is easy to miss because the function is hidden. If a VARCHAR column is compared with an NVARCHAR value, SQL Server may convert the column on every row, which can turn a seek into a scan:

-- EmployeeCode is VARCHAR(20)
WHERE EmployeeCode = N'E1001'  -- NVARCHAR literal: can force a conversion
WHERE EmployeeCode = 'E1001'   -- matching type: clean seek

This happens a lot from application code. In .NET, string parameters default to NVARCHAR; for VARCHAR columns, set the parameter type explicitly (for example SqlDbType.VarChar, or configure the column type in EF Core). The execution plan shows a warning icon for implicit conversions, so it is easy to confirm.

What about OR and NOT?

OR across different columns (WHERE A = 1 OR B = 2) often prevents a single seek. Rewriting it as two queries combined with UNION (or UNION ALL when rows cannot overlap) can let each part use its own index.
<> and NOT IN are technically SARGable, but they usually match so many rows that a scan is the right choice anyway.

When you cannot change the expression

If the calculation is unavoidable, for example you always filter on an expression, you can add a computed column for it and index that column. SQL Server can then seek on the indexed expression:

ALTER TABLE dbo.Orders
ADD OrderYear AS YEAR(OrderDate);

CREATE INDEX IX_Orders_OrderYear ON dbo.Orders (OrderYear);

Queries that use YEAR(OrderDate) = 2026 or OrderYear = 2026 can then use the new index.

How to check your fix

Run the query with the actual execution plan and SET STATISTICS IO ON. A successful rewrite changes an Index Scan into an Index Seek and drops logical reads sharply, often from thousands to a handful.

Quick rules

Keep indexed columns bare in WHERE and JOIN conditions.
Replace date functions with ranges, and LEFT/SUBSTRING with prefix LIKE.
Move arithmetic to the constant side.
Match parameter data types to column data types.
Use an indexed computed column when the expression is unavoidable.

More SQL Server guides on my blog, SQL & Cloud Lab.

DEV Community: LingamSuyambu