Post reply

Does updating the key value on a partioned table cause the row to move partitions?

  • October 26, 2012 at 1:14 pm

    #269110

    I think I know the answer to this, but I want to make sure. There isn't much information I can find that directly addresses this issue, and I'm betting that is in some ways validating that it works the way I think it should.

    On a table that is partitioned with a function, does changing the column that the function uses to determine which partition to place the row on during insert actually move the row, or do I need to do a delete and insert?

    Thanks for the help on this.

    Todd

  • October 26, 2012 at 5:11 pm

    #1553450

    If updating the key then if required SQL Server will move the record to the correct partition per the answer to the partition function. It does this by deleting the record from the previous partition and inserting it into the new partition meaning your update is potentially far more expensive than in a non-partition scenario.

    Proof-of-concept:

    What happens when I update my partitioning key?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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