Post reply

why does it display clustered index scan in the Executed plan ?

  • April 10, 2009 at 1:03 am

    #136562

    The PayCode and DBNo fields were setted the primary key of the table Pb_PayWay,So there is a clustered index in this table, I have not created any other indexs on this table,I use below query but why it display clustered index scan in the Executed plan ?

    select * from pb_payWay where lastupdatedby='admin'

  • April 10, 2009 at 1:13 am

    #975594

    Well, the clustered index keys are the columns in your primary key, ie PayCode and DBNo. Since you're filtering by a column that's not one of those and you have no other indexes, the only way SQL can evaluate that query is by scanning the clustered index, hence clustered index scan.

    What operator were you expecting to see?

    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

  • April 11, 2009 at 2:53 am

    #976018

    I think it will be execute with clustered index when we only select the columns which have clustered index .

    I'm a novice, could you please give some knowledge about index ?

    Many thanks

  • April 11, 2009 at 3:15 am

    #976022

    The clustered index is the table. The leaf level of the clustered index contains the actual data pages of the table (see books online for details on index architecture) so, while only 2 columns are index keys, all of the columns in the table are part of the clustered index.

    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 4 posts - 1 through 3 (of 3 total)

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