What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?
4 Answers
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
-
good example. All the remaining answers are saying the same– ahsanDec 9, 2010 at 18:33
-
1That site is no longer online but here is another useful link: percona.com/blog/2012/11/23/…– B SevenJul 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
Finally
- 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.
http://www.sqlservercentral.com/articles/Performance+Tuning/bookmarklookups/1899/