This is for SQL Server:

I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the partition.

. Can I..

  1. Make a copy of the partition
  2. Apply updates
  3. Merge the updated partition back?

I am worried about speed of updates. Want to do all updates offline (users would be using the partition actively), and, if possible, switch the updated partion in, so that there would be minimal impact to users...

  • Would the updates alter which partition the data belongs too? If not, can't you just update the partitioned data? – Pablo Romeo Aug 20 '12 at 18:49
  • 1
    What do you need to do that can't be achieved through the normal update statement knowing that you can craft a query to just update a single partition anyway? – World Wide DBA Aug 20 '12 at 19:22
  • 2
    ALTER TABLE ... SWITCH will be your best friend. – Jon Seigel Aug 20 '12 at 19:34

You could copy the data into another table which has the same schema and partitioning, then switch out the old data, then switch in the new data.

The better option would probably be to run the update in a loop against small numbers of rows using the TOP syntax.

    UPDATE TOP (1000) YourTable
        SET Something = 'Something'
    WHERE SomeDate = '1/1/2012'
        AND Something <> 'Something'
  • +1 Denny.The Update is certainly the easiest to implement. The top clause will maximize concurrency and minimize the chance of filling the log (putting the db in recovery). It may run for hours/days though. If the record count is fairly low and you have a decent maintenance window, it may actually be the best option. However (you're the IO guru so correct me here), if the entire partition is affected and the record count is high millions/billions, would partition switching, mixed in with a few other minimally logged executions, not be significantly faster and require less IOPS than the Update? – brian Aug 21 '12 at 2:29
  • Probably, except that he says that the partition needs to be kept available which means that users may be writing to it, so data concurrency becomes a big issue there. From a purely IO side of things the partition switching is probably the way to go. Thinking big picture it's probably going to need to be the update loop as un-fun, and un-sexy as it is. – mrdenny Aug 21 '12 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy