May 17, 2012 at 3:01 pm
I'm studying for the 432 exam & have a few questions about SPLIT, MERGE & SWITCH.
When you want to archive old data to archive tables, how is this a better solution than the following?
insert into archivetable
select * from activetable where ....
go
delete from activetable where ....
Is the new method more appropriate for very large tables where a select might take a while?
Based on my understanding, insert/select/delete would also offer these advantages:
You can move archive data to a different file group.
If you're achiving tables with one-to-many relationships, I don't see how you can use a column in a different table as the partitioning column (such as an order date for order line item records). It's easy to add joins to selects & deletes where you need them.
Please enlighten me.
May 17, 2012 at 3:07 pm
dan-572483 (5/17/2012)
I'm studying for the 432 exam & have a few questions about SPLIT, MERGE & SWITCH.When you want to archive old data to archive tables, how is this a better solution than the following?
insert into archivetableselect * from activetable where ....
go
delete from activetable where ....
How long is that likely to take on a table with 100 million rows where you want to move 10 million or so?
How many locks it it likely to take?
How much log space is it likely to take?
Now compare those to what a partition switch will do - almost no time, just a very short schema lock and almost no logging.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply