Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Jan 26 '12 at 14:33

This question came from our site for professional and enthusiast programmers.

A full table scan can only happen on a heap. – Martin Smith Jan 26 '12 at 14:35
@Martin - I would consider a clustered index scan a "full table scan," and those happen plenty. :) – Nick Chammas Jun 11 '12 at 3:46

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
share|improve this answer

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
share|improve this answer
up vote 5 down vote accepted

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

share|improve this answer
good example. All the remaining answers are saying the same – ahsan Dec 9 '10 at 18:33

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.