All 7 Query Optimization & Plans questions

What is the difference between a table scan and an index seek in an execution plan, and why does this distinction matter for performance?

Beginner

Answer

A table scan means SQL Server reads through every single row in a table to find the ones it needs, which becomes slow on large tables, while an index seek means SQL Server uses an index to jump directly to the specific rows that match a query's condition, generally making it significantly faster, especially as table size grows.

-- Without an index, this causes a table scan
SELECT * FROM Orders WHERE CustomerId = 123;

-- With an index on CustomerId, this becomes an index seek
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

Real-world example A reporting query that used to scan an entire million row orders table runs almost instantly after adding an appropriate index, changing the execution plan from a slow table scan to a fast index seek.

Common follow-ups: Is a table scan always a bad thing to see in an execution plan?;How do you know when adding an index will actually convert a scan into a seek?

Indexes;Data Types & Schema Design

All 7 Query Optimization & Plans questions