SQL Server data partitioning

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

• Make a copy of the partition

• Apply updates

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

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'

StackExchange Database Administrators Q#22794, answer score: 2