What is the difference between an index scan and an index seek?
Quick Answer
A seek navigates the B-tree directly to the rows that match a predicate — efficient when the filter is selective. A scan reads the whole index (or table) start to finish — efficient only when you need most of the rows or the table is tiny. A scan on a large table with a selective WHERE is a red flag: usually a missing or unusable index.
A seek uses the index structure to go straight to the matching rows — that's what you want when the filter narrows things down a lot. A scan reads the whole index or table from one end to the other, which is only efficient if you actually need most of the rows or it's a small table. So when I see a scan on a big table with a selective WHERE clause in the plan, that's the first thing I dig into — usually there's no suitable index, or the predicate isn't sargable so it can't seek.
- Seek needs a sargable predicate on a leading index column. A function on the column, a leading wildcard, or an implicit conversion forces a scan.
- A scan isn't always bad — reading 90% of a table is faster as a scan than a million seeks.
- Clustered index scan = scanning the whole table (the clustered index is the table). Table scan = same thing on a heap.
- Watch for a scan with a residual predicate — it read everything then filtered in memory.