Reputation: 643
I have a huge database with a table containing billion of records. I need to do monthly cleanup of this table (delete oldest records based on date field).
Since I need to delete a few hundred million records for one month worth of data, doing a DELETE
or even deleting in chunks takes too long, because of indexes that slows the process.
bcp data out + truncate + bcp data in is also too long.
Now the solution I want to try is to partition the table into different filegroups (one month per partition). I get the part of building the partitions, but how will I delete a filegroup along with its data?
Upvotes: 0
Views: 4691
Reputation: 9806
You can switch partitions to a new table and then drop that table. Filegroups do not really have anything to do with it other than the restriction that the table you switch to must be on the same filegroup. You do not necessarily have to map your partitions to separate filegroups although you may want to do that for other reasons.
Here's a good example of a partition-wise rolloff in sql server.
Upvotes: 1