Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

“Index Seek is good; Index Scan is bad” is a familiar interview answer. It skips the most interesting part of the execution plan: how much work each operator actually did.

Question: What is the difference between an Index Seek and an Index Scan?

Answer: A seek uses an ordered index to navigate to one or more key ranges. A scan reads through an access path to produce its rows. A Table Scan reads a heap; an Index Scan reads an index. The names describe access methods, not a performance verdict.

Think of a library. If you need one book and know its shelf location, going straight to that shelf is sensible. If you need most of the books in a short shelf, reading through that shelf may be simpler than making many separate trips. The SQL Server optimizer makes a cost-based choice from its estimates and available indexes.

My old answer said a scan always touches every row and that a seek reads only qualifying rows. Both are too strong. A seek can navigate to a broad range and read thousands of rows; a scan may stop early when its parent asks for only a few. A seek may also apply a residual predicate after reading a wider range. Look at Actual Number of Rows Read, returned rows, predicates, lookups, and logical reads before judging.

These simple queries give the optimizer different requests against one temporary table. The exact operators can differ by data, index design, statistics, and SQL Server version, so examine your own actual plans.

CREATE TABLE #SeekScanDemo
(
    ItemId int NOT NULL PRIMARY KEY CLUSTERED,
    CategoryId int NOT NULL
);

INSERT #SeekScanDemo (ItemId, CategoryId)
SELECT value, value % 100
FROM GENERATE_SERIES(1, 10000);

SET STATISTICS IO ON;
SELECT ItemId, CategoryId FROM #SeekScanDemo WHERE ItemId = 42;
SELECT ItemId, CategoryId FROM #SeekScanDemo WHERE ItemId BETWEEN 100 AND 9000;
SELECT ItemId, CategoryId FROM #SeekScanDemo;
SET STATISTICS IO OFF;

Actual seek: 1 row read. Captured in SSMS on SQL Server 2025.

Actual scan: 10000 rows read. Captured in SSMS on SQL Server 2025.

GENERATE_SERIES requires SQL Server 2022 or later with compatibility level 160 or higher. The middle query asks for most of the rows even if its plan displays a Seek. The final query asks for every row, which can make a Scan appropriate. There is no reliable 50 percent or 90 percent threshold to memorize. A useful interview answer explains the operator and then measures the work.

For a longer discussion, read my Index Seek versus Index Scan article. Related topics include primary keys and indexes, duplicate indexes, index maintenance, and columnstore indexes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.