CODEWITHSUNDEEP
sqlsql-serverpartitioning
pdube
pdube

Reputation: 643

How to delete a filegroup from a partitioned table (SQL Server 2012)

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

Answers (1)

gordy
gordy

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

Related Questions