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

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.