When is an index scan not a scan? When it’s a seek!
Consider the following query:
USE AdventureWorks GO SELECT * FROM Production.TransactionHistory
As there is no filter on the table, you would expect to see an index scan. Turn on the Actual Execution Plan, and SET STATISTICS IO ON, and have a look at the results:
(113443 row(s) affected) Table 'TransactionHistory'. Scan count 1, logical reads 792, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Just as expected. Let’s now consider the following query:
SELECT * FROM Production.TransactionHistory WHERE TransactionID >= 1
(113443 row(s) affected) Table 'TransactionHistory'. Scan count 1, logical reads 792, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The query returns the same set of rows, and has identical performance, but shows up in the execution plan as a seek!
The take-home message here is that just because you can see an index seek in your query does not mean that performance is great. It means that the table was accessed using a filter you’ve placed on the query. A lesser message, but sometimes overlooked is that a seek can “scan†a long range of values, if each of the values matches the filter.