8

What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?

2
  • A full table scan can only happen on a heap. Jan 26, 2012 at 14:35
  • @Martin - I would consider a clustered index scan a "full table scan," and those happen plenty. :) Jun 11, 2012 at 3:46

4 Answers 4

7

When selectivity of records is very high full table scan is always better than going through index scan.

http://www.techipost.com/single-index-versus-full-table-scan/

Edit by gbn:

Example, the optimiser may decide that it's easier to scan the table/clustered index if it would require many key lookups (eg non-clustered index to clustered indexs for non-key data).

Or you don't have many distinct values in the indexed columns

2
  • good example. All the remaining answers are saying the same
    – ahsan
    Dec 9, 2010 at 18:33
  • 1
    That site is no longer online but here is another useful link: percona.com/blog/2012/11/23/…
    – B Seven
    Jul 2, 2018 at 18:41
11

When the

  • table is small enough there is no practical difference
  • statistically, you'd return most rows anyway

The 2nd case needs qualifying

  • An index scan will replace an index seek if an index is covering
  • An index seek or scan with many rows that requires key/bookmark lookups will be expensive and a table scan could be better

Finally

  • An index scan and a table scan are pretty much the same for clustered indexes
6

If the table

  • is very small
  • the cost of doing an index scan and then a number of bookmark lookups into the base table is more expensive than a full table scan
4

Better in what way?

The optimizer may pick a table scan if an index is not covering, because an index scan would still have to be augmented with a bookmark lookup.

In those cases, a poorly indexed table is no better than an unindexed table.

http://www.sqlservercentral.com/articles/Performance+Tuning/bookmarklookups/1899/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.