Why Your WHERE Clause Ignores a Perfectly Good Index

Matt Pavey

You add an index on a column in the WHERE clause, run the query again, and still get a scan. Before adding another index, I look at how the query uses the column. A function, a mismatched parameter type, or a leading wildcard can prevent SQL Server from using the index to narrow the search.

The term for a condition that supports that kind of access is SARGable, short for search argument-able. It means SQL Server can use the condition to seek into a suitable index. It does not guarantee that the optimizer will choose a seek.

Dates are a useful example. Suppose OrderDate is the first key column in an ordinary rowstore index:

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

The index is ordered by OrderDate, while the filter asks for YEAR(OrderDate). SQL Server generally evaluates that expression against the rows it reads instead of deriving a date range to seek. It might still scan the index, so the index is not necessarily unused. The problem is how much data it has to read.

For an OrderDate stored as date, datetime, or datetime2, express the same condition as a range:

SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE OrderDate >= '20250101'
  AND OrderDate <  '20260101';

Now SQL Server has a starting point and an ending point in the index. The exclusive upper boundary includes all of December 31, even when the column stores a time of day. This makes a range seek possible without having to calculate the year for each candidate row.

The less obvious case is a parameter mismatch. Suppose ProductCode is an indexed varchar(20) column:

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

The query looks straightforward. But when SqlClient infers a parameter type from a .NET string, it uses nvarchar. SQL Server then has two types to reconcile, and nvarchar has higher precedence than varchar. The comparison involves converting the column's value to nvarchar.

Whether that prevents a seek depends partly on the column's collation, its rules for comparing and sorting text. With SQL_Latin1_General_CP1_CI_AS, this mismatch can turn a seek into a scan. Windows collations can preserve the ability to seek. Solomon Rutzky's tests of both cases show why the same mismatch does not always produce the same plan.

I would still match the parameter to the column. In ADO.NET, explicitly set SqlDbType.VarChar and the appropriate size. In Dapper, DbString lets you specify that. For this varchar(20) column, with a product code already validated to fit:

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

Use the type and length your column actually declares. An nvarchar column should receive an nvarchar parameter. The point is to remove an avoidable mismatch, then check whether it was affecting the plan.

Wildcards have a similar practical consequence. LIKE 'John%' supplies a fixed prefix that can support a range seek on an index keyed by the name. LIKE '%son' supplies no such prefix. That condition alone cannot narrow an ordinary index on the name to a useful starting range. SQL Server may scan the index or table, or use another condition to reduce the rows first. Adding the leading wildcard changes the work involved in the search.

Keeping the indexed column unmodified in a comparison is a useful starting rule. If filtering on a transformed value is a recurring requirement, an indexed computed column may be appropriate, provided it meets SQL Server's requirements for the expression and index. That gives the optimizer an index on the value you actually search.

Before calling any of these changes a fix, I check the actual execution plan:

  • Which conditions appear under Seek Predicates, and which are evaluated as residual filters after rows have been located?
  • Is there a CONVERT_IMPLICIT expression on the indexed column?
  • How many rows did the access operator read compared with how many it returned?

A seek can still read a large range and discard most of it. A scan can be sensible for a small table or a query returning a large share of its rows, especially when a seek would require many additional lookups to fetch the selected columns.

I compare logical reads with SET STATISTICS IO ON and check execution time using representative parameter values. Changing a scan icon to a seek icon is only useful if the query returns the right results with less work.