Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com 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

4 Answers 4

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
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.

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

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.

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

share|improve this answer

Your Answer

 
discard

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