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

  • 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


When selectivity of records is very high full table scan is always better than going through index 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

  • 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

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


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

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

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.


Your Answer

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