When is a seek actually a scan?

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

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)


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.


  1. Jason Strate

    Good information, thanks.

  2. Pingback: SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA

  3. Grant Fritchey

    Nice. I see a lot of hints & what not trying to force seeks and loop joins as if they were ALWAYS better than the alternatives. As you’ve just shown, it isn’t always true.

  4. Gail

    Yup, likewise.
    That reminds me, I have a half-written post on optimal joins. Must finish it.

  5. Julian

    Hi Gail Shaw

    Is this test done on SQL 2000 or SQl 2005

    What will be the number of pages read if you

    select * from testseeks

  6. Gail

    SQL 2008 RTM. SQL 2005 should have the same behaviour

    Doing a SELECT * will read the heap, not the index because the index is not covering and the lookups are far too expensive. The operator that appears in the plan is Table Scan

    Table ‘TestingSeeks’. Scan count 1, logical reads 1174

  7. Pradeep Singh

    Wont the 2nd query be slower(using index seek for all rows) than reading directly from the heap as you did in the first query?

  8. Gail

    No, because the seek is just seeking to the first row (on the first page) and then reading all the leaf pages. It’s not as if it’s doing a seek for each value in the table.

    The number of page reads was the same and that, for a simple query like this, is the major factor on speed. But feel free to test it yourself, all the code’s in the post.

  9. Pradeep Singh

    I ran the code and got almost similar execution time results(index seek operation was marginally better than the other). Number of logical pages reads were identical too.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>