Post reply

When do non clustered index are updated

  • November 30, 2014 at 11:26 pm

    #295509

    I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.

    Thanks.

  • November 30, 2014 at 11:34 pm

    #1761601

    Shadab Shah (11/30/2014)

    I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.

    Thanks.

    Really hard to tell without an execution plan, the table definition, the indexes on the table, and knowing which columns have been updated.

    If you updated one of the key columns in the clustered index, it will affect all indexes because the key columns of the CI are added to all NCIs. Also, if INCLUDEs are present in the index and one of the INCLUDEs had data change, the NCI will also be updated.

    There could also be a trigger in play that might affect an index.

    There may be other reasons but, like I said, without a little bit of esoteric knowledge of the table, it's impossible to know the real reason why.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 1, 2014 at 12:08 am

    #1761604

    Jeff Moden (11/30/2014)

    Shadab Shah (11/30/2014)

    I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.

    Thanks.

    Really hard to tell without an execution plan, the table definition, the indexes on the table, and knowing which columns have been updated.

    If you updated one of the key columns in the clustered index, it will affect all indexes because the key columns of the CI are added to all NCIs. Also, if INCLUDEs are present in the index and one of the INCLUDEs had data change, the NCI will also be updated.

    There could also be a trigger in play that might affect an index.

    There may be other reasons but, like I said, without a little bit of esoteric knowledge of the table, it's impossible to know the real reason why.

    Thanks Jeff, I was not knowing that NCI are updated when they have included columns. i guess i should learn more about how covering indexes are store and updated internally.

    Many thanks.

  • December 1, 2014 at 7:05 am

    #1761702

    My pleasure. Thank you for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 3, 2014 at 2:42 am

    #1762296

    Jeff Moden (12/1/2014)

    My pleasure. Thank you for the feedback.

    Hi,

    I search a loot but could not find anywhere how Covering Index are stored intenally.

    The reason i am trying to find out about storing of Covering index is because i would like to know Why Covering Index are updated in an Update statement.

  • December 3, 2014 at 3:08 am

    #1762299

    http://www.sqlservercentral.com/articles/Indexing/68636/

    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

  • December 3, 2014 at 4:18 am

    #1762303

    GilaMonster (12/3/2014)

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Hi Gail,

    I have gone through your completed post but still cannot understand Why Covering index is updated During and update .

    My update does not contain the key column of Clustered Index, Still :ermm:

  • December 3, 2014 at 5:15 am

    #1762311

    Very first sentence in the section "Include columns"

    Columns specified as include columns are stored at the leaf level of the nonclustered 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 8 posts - 1 through 7 (of 7 total)

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