What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?
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
- 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
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.