Change column smallint to int 300 million records

  • Indianrock (2/11/2016)


    Management's main concern is no blocking/locking.

    That's where following the clustered index order comes to play.

    You lock the continuous chunk of data and remove it.

    Minimal number of pages affected, all done in a single pass.

    When with un-ordered 1 record removal you update the same page as many times as many rows fit that page, and every time you delete a single row from a page every index needs to be updated, which means - several pages of it will locked with an exclusive lock.

    And the same pages in all indexes will be locked as many times as many rows you delete from a single data page.

    _____________
    Code for TallyGenerator

Viewing post 46 (of 45 total)

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