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.

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.