The Four Ways SQL Server Reads Data
Every operator that pulls rows out of a table or index in an execution plan is one of these four. Nothing else exists at this level: whatever the query looks like, the storage engine ends up doing one of these things to get the data.
| Operator | What it reads | Cost scales with |
|---|---|---|
| Table Scan | Every page of a heap (a table with no clustered index), in whatever physical order they happen to sit in | Table size |
| Index Scan | Every leaf page of an index (clustered or nonclustered), in logical key order, ignoring the tree above the leaf level | Index size (in scope) |
| Index Seek | Only the leaf page(s) that can contain a match, reached by navigating the B-tree from the root | Tree height — barely moves as the table grows |
| Key Lookup (RID Lookup on a heap) | One extra single-row trip into the clustered index (or heap), per row a nonclustered seek/scan already found, to fetch columns the index doesn't carry | Number of matching rows, not table size |
The first three are alternatives: a given read is a scan, or it's a seek, never both. A Key Lookup is different — it's an add-on, always riding on top of a seek (or a scan) against a nonclustered index, because that index alone doesn't hold every column the query needs.
Table Scan
A Table Scan happens against a heap — a table with no clustered index. Rows aren't kept in any particular order, so there's nothing to navigate: the engine reads every page, start to finish, and evaluates the predicate against every row it passes.
No order, no shortcut: every page is read whether it holds a match or not. Hover to pause.
Note what a Table Scan does not depend on: how selective the predicate is. Two matches out of a million rows costs exactly the same number of page reads as two million matches, because the only way to find them is to look at everything.
Index Scan
An Index Scan reads every leaf page of an index — clustered or nonclustered — in logical key order. The pages are sorted, unlike a heap, but the scan doesn't use that: it never touches the root or intermediate levels, so the sort order buys it nothing except "leaf pages happen to come out in key sequence" and (for a range) the ability to stop early once past the range.
Same B-tree the seek would use, but the scan bypasses it and just walks the leaf level. Hover to pause.
This is the detail that trips people up: "it's on an index" doesn't mean "it seeks." Clustered Index Scan and Table Scan are, in effect, the same operation — read the whole thing — the plan just uses the other name because the table happens to have a clustered index instead of being a heap.
Index Seek
An Index Seek uses the tree structure an Index Scan ignores: it starts at the root, compares the search key against the page's separator values, follows exactly one pointer down, and repeats until it lands on the leaf page(s) that can contain a match. Every level it passes through eliminates everything else on that page without reading it.
Two page reads here, three on a taller tree — height, not table size, is what a seek pays for. Hover to pause.
Compare the pacing of this animation against the two above: it finishes in a couple of steps while the scans keep going page after page. That's the entire performance argument for indexing in one picture. For the full multi-level walkthrough (root → intermediate → leaf) and what makes a predicate SARGable enough to trigger a seek at all, see SQL Server B-Tree: How an Index Seek Finds One Row.
Key Lookup: The One That Sneaks Up on You
A nonclustered index doesn't store every column of a table, only its key column(s) plus (optionally) whatever's in an INCLUDE list. When a query needs a column the index doesn't carry, SQL Server has to go fetch it — and it does that with a separate, single-row trip into the clustered index (or a RID Lookup into a heap), for every matching row.
The seek that finds the rows is cheap. What it finds them in — an index missing a column — is where the cost actually lives. Hover to pause.
Watch how the pacing changes: the seek into the nonclustered index takes two quick steps, same as any seek. Then the cost starts stacking up one row at a time. Three matches, three lookups here — three million matches, three million lookups, each its own single-row random read. That's why a plan can show "Index Seek" as its top operator and still be the slowest thing in the batch: read the Number of Executions on the Key Lookup operator, not just its per-row cost.
Fixing a Key Lookup
- Add the missing columns to the index's
INCLUDElist. This makes the nonclustered index covering for that query: everything it needs is in the leaf page already, no second trip required. - Narrow the covered column list. A wide
SELECT *often drags in columns the query doesn't actually need; trimming the select list can turn a lookup-heavy plan into a fully covered seek without touching the index at all. - Leave it alone if the match count is small. A handful of lookups per execution is often cheaper than maintaining a wider index on every write. This is a cost trade-off, not an automatic fix.
Seek vs. Scan vs. Lookup, Side by Side
| Operator | Uses the B-tree? | Reads per execution | Grows with |
|---|---|---|---|
| Table Scan | No — heap has no tree | Every page in the table | Table size |
| Index Scan | No — bypasses it, reads leaf level only | Every leaf page in scope | Rows in scope |
| Index Seek | Yes — root to leaf | ~tree height (a few pages) | log(table size) — barely |
| Key Lookup / RID Lookup | Yes, per lookup — but a fresh trip each time | ~tree height, repeated once per matching row | Number of matching rows |
Spotting Them in a Real Plan
In SSMS or Azure Data Studio, hover any operator icon and the tooltip names it exactly:
Table Scan— heap, full read.Clustered Index Scan— same idea, but the table has a clustered index; still a full leaf-level read.Index Scan— a nonclustered index read leaf-to-leaf, no seek predicate used.Index Seek/Clustered Index Seek— tree navigation happened. Click it and check the Seek Predicate property: that's what actually narrowed the page reads. A plain Predicate (no "Seek") is a residual filter applied after landing on the page — it doesn't reduce reads.Key Lookup/RID Lookup— usually drawn as a thin operator feeding into a Nested Loops join right next to the seek/scan that produced its input. Check its Number of Executions, not just its estimated cost: cost per row can look tiny and still add up.
Best Practices
- ☐ Don't chase "Seek" as a goal by itself — a scan is correct and often cheaper on small tables or when the query needs most of the rows anyway.
- ☐ Prefer a clustered index over a heap on any table of real size; a heap only ever offers Table Scan or RID Lookup, never a seek of its own.
- ☐ When a plan shows Index Seek but is still slow, look one operator over for a Key Lookup with a high Number of Executions.
- ☐ Add narrow, non-key columns via
INCLUDEto make a hot nonclustered index covering, eliminating its Key Lookup entirely. - ☐ Keep predicates SARGable so the optimizer can even attempt a seek in the first place — see Never Put Functions in WHERE Clauses.
- ☐ Read Seek Predicate vs. plain Predicate on every seek: a seek with a loose seek predicate can still touch a wide range of leaf pages.
CREATE INDEX statement (typically an INCLUDE addition) for exactly this situation, and QP010 separately ranks Table/Index/Clustered-Index Scans by average logical reads per execution so you know which one to fix first. See the execution-plan analysis documentation for details.
The Bottom Line
Four operators, one underlying question: how much of the storage did SQL Server have to touch to answer this query? A Table Scan and an Index Scan touch all of it. An Index Seek touches almost none of it, because the B-tree lets it skip straight to the answer. A Key Lookup is a seek's cost repeated once per row it's asked to fetch, which is why "the plan says Seek" is never, by itself, proof that a query is fast.