At my work I have inherited a database where the partitioning was not done correctly. This database holds millions and millions of records for each year. Hence the need for partitioning.
So I started working to correct/improve the same in a dev environment. The current table (Table1) that is partitioned is based on filegroup.
After a lot of experiments, I sought feedback from Partitioning expert Martin Cairney
He suggested me to correct this situation in the below steps.
Step 1. Create a new partition function (yearly_pf) and partition scheme (yearly_ps) that reflects the correct partitions.
The code I used for this is as below.
Step 2. Create another table (Table 3) with the same schema as the existing table Table1. But base this on the partition scheme rather than on filegroup.
For this I scripted the create table script from Table 1 as this will all the constraints of the existing table. But I changed the On filegroup statement to On yearly_ps
Step 3. Create a staging table (Table 2) to insert data from Table 1 on a year by year basis.
For this I used the create table script from Table 1
Step 4. Insert the data from the staging table with a where clause that includes yearly data into Table 3.
Step 5. After all the data is inserted into Table 3, rename Table 1 an rename Table 3 to Table 1. When you are satisfied with everything, then drop Table 1.
I did this process from 2007 to 2012.
After that I started wondering do we really need the staging table.
Since this is a dev environment, I thought I will try without the staging table.
So I repeated the steps 1 to 2 above after dropping Table 3. Then I started to Insert data straight into Table 3 on a yearly basis. I checked every time whether the yearly data is going to the correct partition or not.
The script I used for this is as follows.
The process was smooth.
I would like to implement this in Production. I would like some feedback of the viewers of this blog as to whether I am doing any blunders here.