In this lesson, you will learn how the database engine decides between the precision of an index seek and the brute force of an index scan when retrieving data ranges.
The Cost of Navigating the B-Tree
An Index Seek is like looking up a word in a dictionary: you use the sorted structure to jump directly to a specific value. However, every "seek" carries an overhead cost. Each time the engine performs a seek, it must traverse the B-Tree from the root node down to the leaf level, involving multiple I/O operations and CPU cycles to navigate the index branches.
An Index Scan, conversely, is like reading a page from start to finish. It is highly efficient for sequential I/O because once the engine reaches the starting point, it simply follows the "linked list" pointers between leaf nodes.
The Threshold Concept
The engine must determine the "tipping point"—the volume of data where performing repeated seeks becomes more expensive than simply scanning the leaf pages.
Consider a query looking for customers with an ID between 100 and 500:
SELECT * FROM Customers WHERE CustomerID BETWEEN 100 AND 500;
If your query filters for a very small range (e.g., 5 rows), the engine will perform a seek. But if you increase that range to 50,000 rows, the cost of repeating the seek-and-lookup process for each row (or even every page) starts to exceed the cost of one continuous scan. The engine estimates the "selectivity"—the percentage of the total index the query touches.
Generally, when a query retrieves more than roughly 5% to 20% of an index, the optimizer realizes that the overhead of traversing the B-Tree repeatedly for individual records is slower than sequentially reading the relevant leaf pages in one go.
Why It Matters
When you write queries with large BETWEEN ranges or broad LIKE patterns, don't be surprised to see a "Scan" in your execution plan. This is not necessarily a failure; it is the optimizer choosing the most efficient path based on the density of the data you are requesting.
Key Takeaway: While a seek is precise, the overhead of navigating the index tree makes it less efficient than a sequential scan once a query needs to retrieve a significant percentage of the index's total data.