Why Your WHERE Clause Ignores a Perfectly Good Index

Matt Pavey

You built the index. The column is right there in the WHERE clause. And the query still crawls. You pull up the execution plan and there it is: an index scan where you expected a seek, SQL Server reading every row in the table to answer a question the index should have settled in a few pages. Most of the time this is not the index's fault. Something in the WHERE clause hid the column from it.

There is a name for what makes this work when it works: SARGable, from "search argument." A predicate is SARGable when the optimizer can answer it by seeking into an index. When it cannot, the engine has to check your condition against every row in the table, and that is your scan. Usually what took away the seek is doing something to the indexed column itself, rather than to the value you are comparing it against.

Dates are where this shows up most. Here is a query that looks completely fine:

SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2025;

The problem is YEAR(). It wraps the indexed column in a function, so before SQL Server can compare anything it has to compute YEAR(OrderDate) for every single row, and once it is doing that, the index on OrderDate is useless to it. Ask the same question as a range instead, and the column is back out where the index can see it:

SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2026-01-01';

Same answer, but now it is a seek. The index goes straight to the rows in that date range instead of the engine reading the whole table to find them.

The version that catches more people has no function in it at all. Say ProductCode is a varchar column with an index, and you query it the obvious way:

SELECT ProductId, Name
FROM dbo.Product
WHERE ProductCode = @code;

Nothing is wrapped around the column. It looks clean. But if @code comes in as an nvarchar, which is what a .NET application sends by default, SQL Server has two different types to reconcile, and nvarchar wins that contest. So rather than convert your parameter, the engine converts the column, every row of it, which is the same as writing CONVERT(nvarchar, ProductCode) yourself. You are back to a function on the column, except you never typed it, and the query looks innocent while the plan quietly scans.

The fix is to hand the parameter over as the type the column already is. In plain ADO.NET, set the parameter's SqlDbType to VarChar. In Dapper, wrap the value so it goes across as ANSI:

var rows = conn.Query<Product>(
    "SELECT ProductId, Name FROM dbo.Product WHERE ProductCode = @code",
    new { code = new DbString { Value = code, IsAnsi = true, Length = 20 } });

One property, and the seek comes back. This one is easy to miss: the SQL is correct, the index is there, and the only thing wrong is a type mismatch that never appears in the query text. I have watched a developer rewrite the same query five different ways hunting for the problem when the problem was the parameter the whole time.

Wildcards do a smaller version of the same thing. LIKE 'John%' can seek, because SQL Server knows exactly where in the index to start looking. LIKE '%son' cannot, because a leading wildcard gives it no starting point, so it falls back to reading the whole table. A wildcard at the end is fine; a wildcard at the front turns the search into a scan. Good to know before you promise someone a fast "search anywhere in the name" feature.

Underneath all three is one rule. Leave the indexed column alone on its side of the comparison, and do whatever work you need on the other side. If you genuinely have to filter on a transformed version of a column, a date pulled from a timestamp, a value dug out of a longer string, you can index the transformation itself with a computed column and get your seek back. But that comes second. First, check that you are not hiding the column from its own index without realizing it.

The nice thing is that none of this is guesswork. The execution plan tells you, in plain language, whether each step is a seek or a scan. When a query is slower than its indexes say it has any right to be, that plan is where I start, and a scan sitting where a seek belongs almost always comes back to something done to the column that did not need doing.