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.


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.

| improve this answer | |
  • Hi Aaron. I'm sure you are right but it's me that couldn't quite get it :( So think about this query select * from tableX where name='anyName' and we have a nonclustered index (let index name be: ncName) on name column. Please correct me if I'm wrong but optimizer would either do a seek on ncName or scan on clustering key. Where does nc scan fits here? Thanks. – Stackoverflowuser Oct 22 '14 at 14:39
  • 2
    Non-clustered index scan shouldn't be chosen there unless there are a lot of rows that match a specific name (or statistics are way off-base). For a few rows, it might make more sense to do an index seek on the name column coupled with a lookup to get the other columns in *. But unless there is more to the story I don't see off-hand why a non-clustered index scan would be chosen in that case. – Aaron Bertrand Oct 22 '14 at 14:43
  • What about non-clustered index scan performed whilst calculating an aggregate on a table? Comparing with your scenario given: 1. Cost is not relevant as it is necessary to scan all rows. 2. Even if non-clustered index is as narrow (No, I am not going to say “skinnier”!) as clustered – non-clustered will still be preferred. 3. Non-clustered index not necessarily may cover columns needed by the query. – Pavel Nefyodov Oct 24 '14 at 22:09
  • @Pavel sure, I wasn't saying "This is the ONLY case where a non-clustered index scan would be chosen." Especially given the follow-up comment by the OP. Anyway, I'd argue your scenario still fits into #1 - cost is absolutely considered. You're getting an aggregate that could be satisfied by an NC or CIX scan; SQL Server is going to choose the cheaper one. If they're the same, it's a coin toss. – Aaron Bertrand Oct 24 '14 at 22:18
  • Just wondering if you are aware of any examples when given equal cost CI will be preferred over NC? – Pavel Nefyodov Oct 24 '14 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.