What is a non-clustered index scan

Posted on

Question :

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered index scan?

Thank you.

Answer :

A non-clustered index scan may be chosen in this scenario:

  1. the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans
  2. the non-clustered index is “skinnier” than the clustered index
  3. the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still cheaper than a clustered index scan)

It can also happen, obviously, if you have a heap (no clustered index) and 3. is still true.

This is not meant to be an exhaustive list – there are other cases where a non-clustered index scan may be chosen, but this is probably the most common.

Leave a Reply

Your email address will not be published. Required fields are marked *