Post reply

What happens at the page level on updates?

  • February 10, 2012 at 11:45 am

    #251359

    On update, does the data row in the page get deleted and reinserted in the same place on the page or does it get relocated somehow? I don't know if I'm asking this question properly or using the proper verbiage, but basically I want to know if UPDATE statements can cause page splits.

  • February 10, 2012 at 11:59 am

    #1445719

    http://sqlinthewild.co.za/index.php/2011/06/21/are-all-updates-split-into-delete-insert/

    Yes, update can cause page splits in 2 situations

    1 - The update grows the row (variable length column increases or null value becomes not null) and there is not space on the page for the larger row

    2 - The update changes the index key value, moving the row to a new page and the destination page is full.

    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

  • February 10, 2012 at 12:05 pm

    #1445724

    Thank you very much. Exactly what I needed to know.

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

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