Seeks, scans and SARGable predicates

An index seek navigates the B-tree from the root straight to the first qualifying key and reads only the range it needs. An index scan reads the whole index (or the whole table, for a clustered index or heap scan) and tests every row.

A scan is not automatically wrong. If the query needs a large share of the rows, reading everything in order can be cheaper than thousands of seeks plus key lookups, and the optimizer chooses on estimated cost. The problem is a query that wants a handful of rows and cannot seek.

SARGable: the column alone on one side

A predicate is SARGable (Search ARGument-able) when the optimizer can turn it into a range of index keys. The rule of thumb: the bare column on one side, an expression that does not depend on the row on the other.

Use a half-open range (>= start, < next start) for dates and times. BETWEEN '2024-03-01' AND '2024-03-31' on a DATETIME2 column stops at midnight of the 31st and silently loses the rest of that day.

One known exception: CAST(datetime_col AS DATE) = '20240105' can still seek, because SQL Server special-cases that conversion. Do not generalise it to other functions.

The probes below use FORCESEEK again: a predicate the optimizer cannot seek on fails to compile with error 8622. This shows what is possible, not what the optimizer would choose without the hint.

1CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, placed_on DATE NOT NULL, placed_at DATETIME2 NOT NULL);2CREATE INDEX ix_customer ON orders (customer_id);3CREATE INDEX ix_placed_on ON orders (placed_on);4CREATE INDEX ix_placed_at ON orders (placed_at);5INSERT INTO orders VALUES (1, 5, '2024-01-05', '2024-01-05 10:00'), (2, 6, '2023-12-31', '2023-12-31 23:00');

1DECLARE @probes TABLE (id INT, predicate NVARCHAR(80));2INSERT INTO @probes VALUES3  (1, N'YEAR(placed_on) = 2024'),4  (2, N'placed_on >= ''20240101'' AND placed_on < ''20250101'''),5  (3, N'customer_id + 0 = 5'),6  (4, N'customer_id = 5'),7  (5, N'CAST(placed_at AS DATE) = ''20240105''');8 9DECLARE @log TABLE (id INT, predicate NVARCHAR(80), can_seek NVARCHAR(30));10DECLARE @i INT = 1, @p NVARCHAR(80), @q NVARCHAR(300);11WHILE @i <= 512BEGIN13  SELECT @p = predicate FROM @probes WHERE id = @i;14  SET @q = N'SELECT COUNT(*) FROM orders WITH (FORCESEEK) WHERE ' + @p;15  BEGIN TRY16    EXEC sp_executesql @q;17    INSERT INTO @log VALUES (@i, @p, N'yes');18  END TRY19  BEGIN CATCH20    INSERT INTO @log VALUES (@i, @p, CONCAT(N'no, error ', ERROR_NUMBER()));21  END CATCH;22  SET @i += 1;23END;24 25SELECT predicate, can_seek FROM @log ORDER BY id;

Implicit conversion

When two sides of a comparison have different types, SQL Server converts the one with lower data type precedence to the other. NVARCHAR ranks above VARCHAR, so comparing a VARCHAR column with an N'...' literal or an NVARCHAR parameter converts the column, row by row. Under a SQL collation such as this database's SQL_Latin1_General_CP1_CI_AS, that makes the index on the column unseekable. Under a Windows collation such as Latin1_General_CI_AS, SQL Server can still seek through the conversion, as the last probe shows, but the column is converted all the same. The mismatch is a classic in applications whose driver sends every string as NVARCHAR. Match the parameter type to the column type.

1CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(60) NOT NULL);2CREATE INDEX ix_email ON users (email);3INSERT INTO users VALUES (1, 'user5@example.com'), (2, 'nino@example.com');4CREATE TABLE users_win (id INT PRIMARY KEY, email VARCHAR(60) COLLATE Latin1_General_CI_AS NOT NULL);5CREATE INDEX ix_email_win ON users_win (email);6INSERT INTO users_win VALUES (1, 'user5@example.com'), (2, 'nino@example.com');

1DECLARE @probes TABLE (id INT, tbl SYSNAME, predicate NVARCHAR(80));2INSERT INTO @probes VALUES3  (1, N'users', N'email = ''user5@example.com'''),4  (2, N'users', N'email = N''user5@example.com'''),5  (3, N'users', N'email LIKE ''user5%'''),6  (4, N'users', N'email LIKE ''%@example.com'''),7  (5, N'users_win', N'email = N''user5@example.com''');8 9DECLARE @log TABLE (id INT, tbl SYSNAME, predicate NVARCHAR(80), can_seek NVARCHAR(30));10DECLARE @i INT = 1, @t SYSNAME, @p NVARCHAR(80), @q NVARCHAR(300);11WHILE @i <= 512BEGIN13  SELECT @t = tbl, @p = predicate FROM @probes WHERE id = @i;14  SET @q = N'SELECT COUNT(*) FROM ' + QUOTENAME(@t) + N' WITH (FORCESEEK) WHERE ' + @p;15  BEGIN TRY16    EXEC sp_executesql @q;17    INSERT INTO @log VALUES (@i, @t, @p, N'yes');18  END TRY19  BEGIN CATCH20    INSERT INTO @log VALUES (@i, @t, @p, CONCAT(N'no, error ', ERROR_NUMBER()));21  END CATCH;22  SET @i += 1;23END;24 25SELECT tbl AS table_name, predicate, can_seek FROM @log ORDER BY id;

Common mistakes

• Wrapping the column in a function (YEAR, ISNULL, UPPER, LTRIM) and expecting the index to help.
• BETWEEN with an end-of-day date on a column that has a time part.
• Passing NVARCHAR parameters for VARCHAR columns. The second example shows the same comparison seekable with one literal type and not with the other.
• Treating every scan as a bug. Returning most of a table is a scan's job.

What interviewers ask

• What does SARGable mean? Give an example of a non-SARGable predicate and its fix. A predicate that can become an index range; YEAR(d) = 2024 becomes a half-open date range.
• Why can a query on an indexed VARCHAR column still scan? An NVARCHAR parameter forces a conversion of the column, which under a SQL collation rules out a seek.
• Is a scan always worse than a seek? No; for a large fraction of the rows a scan avoids per-row lookups.