Post reply

Effect of Clustered Index Updates

  • January 27, 2012 at 9:06 am

    #1439447

    Grizzly Bear (1/27/2012)

    But it definitely does make the NC index move around from page to page (or I should say could make as it could make within the same page) -- there by causing fragmentation.

    Generally not.

    If the clustered index key is just there by default, it'll be the last column of the key (non-unique nonclustered index) or an include column (unique nonclustered index). Hence, if the leading columns of the key haven't changed, the row won't need to move pages in the nonclustered index (except possibly in cases where there are duplicate key values spanning more than one page). It'll never move within a page, rows on a page have no defined order, if a row's order within a page has to change, the slot array is altered, the row isn't moved.

    Now if the clustering key is increased in length (variable-length column) and the new row no longer fits on the index page, that'll cause a page split and fragmentation.

    p.s. A general recommendation for the clustering key is non-changing. Not because of fragmentation of the nonclustered indexes, but because of the cost of deleting the row from the cluster, reinserting it and then updating all the nonclustered indexes.

    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

  • January 27, 2012 at 9:09 am

    #1439450

    On the topic of index rebuilds, and this is true for SQL 2005, 2008, 2008 R2 and afaik 2012...

    Rebuilding the clustered index just rebuilds the clustered index, no effect on any non-clustered index.

    Rebuilding a non-clustered index just rebuilds that non-clustered index, no effect on anything else.

    On 2008, rebuilding a heap requires that all nonclustered indexes are rebuild as well (the RIDs change, hence the nonclustered indexes must be recreated)

    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

  • January 27, 2012 at 11:55 am

    #1439560

    You are correct I think Gail.

    Nicely explained.

Viewing 3 posts - 16 through 17 (of 17 total)

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