Post reply

Why clustered index scan is called everytime instead of Non-clusered index?

  • October 9, 2009 at 4:10 am

    #216154

    I would like to know why non-clustered index on three or more columns does not work even when the columns are put in the same order in Where clause as in the index and instead of that clustered index scan is called in the Execution plan.

    I found through search that Non clustered index is not called because my where clause was not enough selective but what if I need more data and retrieval should be fast.

    Amit Gupta

    Sr. Software developer

    New Delhi

  • October 9, 2009 at 8:22 am

    #1064096

    Well, without more details it's hard to give you an answer. Basically the Query Optimzer has decided that, given the available options, a clustered index scan is the fastest access method. It could be that your non-clustered index does not cover the query (include all the columns returned in the select list). If you post some DDL and a query for an example someone may be able to be more specific.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 9, 2009 at 9:00 am

    #1064133

    Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply