What SARGable Means

SARG is short for "Search ARGument." A predicate is SARGable when SQL Server can use it to navigate an index B-tree directly to the matching rows — an Index Seek. A predicate is non-SARGable when the engine must first compute something for every row, then check it — which means reading every row, which means a scan.

The rule, in one sentence: the column must appear alone on one side of the comparison.

SARGable:      Column  <operator>  expression
Not SARGable:  f(Column) <operator> expression

That is nearly the whole subject. Everything in this module is a specific instance of it.

Why it works that way

An index is sorted by the column's stored values. To seek, the engine compares a value against those stored values and walks the tree. If you write YEAR(OrderDate) = 2026, the index is sorted by OrderDate, not by YEAR(OrderDate) — there is no ordering to navigate. The engine's only option is to compute YEAR() for every row and keep the ones matching. Every row means a scan.

The knock-on effect is worse than the scan. Since there are no statistics on YEAR(OrderDate), the optimizer also cannot estimate how many rows will match, so it guesses. A bad guess produces a bad plan for everything downstream (Module 2). So a non-SARGable predicate costs you twice: a scan, and a bad estimate leading to bad joins and bad memory grants.

The catalogue

Every one of these has a rewrite:

| Not SARGable | Rewrite | |---|---| | WHERE YEAR(OrderDate) = 2026 | WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01' | | WHERE CAST(OrderDate AS date) = @d | WHERE OrderDate >= @d AND OrderDate < DATEADD(DAY,1,@d) | | WHERE UPPER(LastName) = 'SMITH' | WHERE LastName = 'SMITH' (collations are case-insensitive by default) | | WHERE Total * 1.1 > 1000 | WHERE Total > 1000 / 1.1 | | WHERE Qty + 5 > @n | WHERE Qty > @n - 5 | | WHERE ISNULL(Region,'') = @r | WHERE (Region = @r OR (Region IS NULL AND @r = '')), or fix the nullability | | WHERE LEFT(Code,3) = 'ABC' | WHERE Code LIKE 'ABC%' | | WHERE Name LIKE '%smith' | needs a different approach — see Lesson 4 | | WHERE DATEDIFF(DAY, OrderDate, GETDATE()) < 30 | WHERE OrderDate >= DATEADD(DAY,-30,CAST(GETDATE() AS date)) | | WHERE VarcharCol = @nvarcharParam | fix the parameter type — Lesson 3 | | WHERE CONVERT(varchar, Id) = '123' | WHERE Id = 123 |

Note the pattern in the arithmetic rewrites: move the computation to the side of the comparison that does not have the column. The engine happily computes 1000 / 1.1 once; it will not compute Total * 1.1 once, it computes it a million times.

Demonstrate it

DROP TABLE IF EXISTS #Orders;
CREATE TABLE #Orders (
    OrderId   int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    OrderDate datetime2(0)  NOT NULL,
    Total     decimal(18,2) NOT NULL
);

INSERT INTO #Orders (OrderDate, Total)
SELECT TOP (200000)
       DATEADD(MINUTE, -(ABS(CHECKSUM(NEWID())) % 1500000), SYSDATETIME()),
       (ABS(CHECKSUM(NEWID())) % 100000) / 100.0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate ON #Orders (OrderDate) INCLUDE (Total);

SET STATISTICS IO ON;

-- Not SARGable: function on the column -> index scan
SELECT COUNT_BIG(*), SUM(Total) FROM #Orders
WHERE YEAR(OrderDate) = YEAR(SYSDATETIME());

-- SARGable: half-open range on the raw column -> index seek
DECLARE @from datetime2(0) = DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1),
        @to   datetime2(0) = DATEFROMPARTS(YEAR(SYSDATETIME()) + 1, 1, 1);

SELECT COUNT_BIG(*), SUM(Total) FROM #Orders
WHERE OrderDate >= @from AND OrderDate < @to;

SET STATISTICS IO OFF;

Turn on the actual plan and run both. Same rows, same answer; one seeks, one scans, and the logical read counts in the Messages tab differ substantially.

One honest qualifier

Making a predicate SARGable does not guarantee a seek. If the predicate matches 70% of the table, the optimizer will correctly choose a scan anyway — a seek returning most of the table plus lookups is slower. SARGability gives the optimizer the option to seek and an accurate estimate to decide with. That is the point: you are restoring choice, not forcing an outcome.

The short version

SARGable means the column is bare on one side of the comparison, so the engine can navigate the index to it. Wrap a column in a function and you lose two things: the seek, and the cardinality estimate, because there are no statistics on the computed expression. The estimate is often the more expensive loss, because it propagates into every join downstream.