I am planning on partitioning a big table. the objective is to have data older than 1 year on a different file group than the rest of the data.
so here is what i am thinking:
Partition boundry Filegroup
Partition 1 <= 09/ 2007 FG_2
Pastition2 10/2007 FG1
Partition3 11/2007 FG1
Partition4 12/ 2007 FG1
Partition5 1/2008 FG1
Partition6 2/2008 FG1
Partition7 3/2008 FG1
Partition8 4/2008 FG1
Partition9 5/2008 FG1
Partition10 6/2008 FG1
Partition11 7/ 2008 FG1
Partiton12 8/2008 Primary (with the rest of my database)
Partition13 empty
When September rolls around, I want October of 2007 to move to FG2
Then I want August data to go to FG1
And septemeber data to be on primary…
what is happening when I merge partition1 and partition2, is that they both get to be on FG1, freeing FG2. but my goal is to do the oposit, I want to merge the 2 partitions, but I need them to stay on FG2
Anyone has any advice on how to acomplish that?