SARGability in SQL Server: Why Some WHERE Clauses Can Never Use an Index

SARGable (Search ARGument-able) means a predicate is written in a form SQL Server can use to seek an index. Write it wrong, and the index sits there unused — no error, just a silent table scan.

The Classic Killer: Wrapping the Column

This directly extends the LIKE-performance aside from Fundamentals Ch.3 — SARGability is the general rule that specific warning was a preview of.

-- NOT SARGable — the function wraps the column, defeating the index
SELECT * FROM dbo.Orders WHERE YEAR(order_date) = 2024;

-- SARGable — the column itself is untouched, range is expressed instead
SELECT * FROM dbo.Orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Why This Happens

An index is sorted by the raw column value. The moment you apply a function to the column in the predicate, SQL Server can no longer use that sort order directly — it must evaluate the function per row, which means scanning.

The Silent Version: Implicit Conversion

-- Orders.customer_code is VARCHAR(20)
-- NOT SARGable — comparing VARCHAR to an implicit int-to-varchar (or worse) conversion
SELECT * FROM dbo.Orders WHERE customer_code = 12345;  -- literal is int, column is varchar

-- SARGable — matching types
SELECT * FROM dbo.Orders WHERE customer_code = '12345';

This one is genuinely dangerous because it produces no error and often no obvious symptom in small tests — only under real data volume does the scan become visible. Data type mismatches between application code and column definitions are a very common, very silent SARGability killer.

Other Common Non-SARGable Patterns

• WHERE column LIKE '%something' — a leading wildcard can't seek (trailing wildcard 'something%' still can)
• WHERE column + 1 = 100 — arithmetic on the column instead of the constant
• WHERE ISNULL(column, '') = 'value' — wraps the column in a function again