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
So a read of the entire index is 935 pages. Now, let me add a filter.
select id from TestingSeeks where id>0
That predicate is SARGable and there is an appropriate index. Sure enough, we get an index seek here.
That’s good. Isn’t it?
Well, not really. That filter’s going to match all the rows in the table. We know there are none with an id less than 1. Statistics IO tells me that 935 pages were read, exactly the same as for the scan. It’s a seek operation, but it’s done exactly the same work as the scan did.
Moral of the story: A seek doesn’t always read only a portion of the index, a seek on an index is not necessarily doing less work than a scan on the same index and silly tricks intended to force an index seek are not going to make a query run faster.