Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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...

share|improve this question
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? – Mr.Brownstone Aug 20 '12 at 19:22
2  
ALTER TABLE ... SWITCH will be your best friend. – Jon Seigel Aug 20 '12 at 19:34

migrated from stackoverflow.com Aug 20 '12 at 19:11

1 Answer

up vote 2 down vote

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.

SELECT NULL
WHILE @@ROWCOUNT <> 0
    UPDATE TOP (1000) YourTable
        SET Something = 'Something'
    WHERE SomeDate = '1/1/2012'
        AND Something <> 'Something'
share|improve this answer
+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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.