Most people who know SQL execution plans will say, without reservation, that an index seek on a particular index is better than an index scan on the same index. In the vast majority of cases, that’s true, but there are times when what appears in the execution plan as an index seek is actually an index scan.
Let me show an example
CREATE TABLE TestingSeeks ( id int identity (1,1) not null, SomeStr char(6) default '' -- a filler ) GO insert into TestingSeeks (SomeStr) select top (500000) '' from sys.columns c1 cross join sys.columns c2
We have a table here with an identity column on it, starting at 1 and incrementing by 1 row. Hence, there will be no negative values in the table. I’m going to then put a nonclustered index on that column (the table has no cluster, it’s a heap)
CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)
Fair enough. If I query all the rows in the table and retrieve just the ID column, I’ll get a scan on that index, as is pretty much expected and Statistics IO tells me that 935 pages were read