Remove old partitions from a partition table, you should either issue a Delete or Switch command. BOL (Books Online) also suggests that while Switching partitions, you should set up a Non-Partitioned Table in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.
You might have multiple filegroups within a partition scheme, it is not feasible to create NON-partitioned table on every filegroup for the Archiving\Purging purpose. This tip shows how you can switch multiple partitions by creating another partitioned table using the same Partition Scheme as the original table.
To start with, let’s create a sample database which contains several filegroups for our demo purposes.
–Create the Database
–Now Create Five Filegroups
Now let’s create the files for each filegroup.
–Now Add One File to Each Filegroup
We then create our partition function.
–Now Create a Partition Function
Then we create our partition scheme.
|–Now create a Partition Scheme to Hold Five Filegroups
–These Five Filegroups will hold the data for the five dates defined in Partition Function.
We create a new partitioned table.
|–Now create a Partitioned Table and clustered index on partition key.
We load some sample data.
Here we check to see how the data was loaded and how many rows are in each partition.
Output of above query Something like below: