I am working on a Sliding Window Data Partitioning Scenario based on date column. The table now has 40M recs and expected to increase by 20M each month.
I want to have 12 Partitions for the latest 12 weeks and all the remaining in 1 historic partition.
With every new week, the oldest week should merge with the historic partition and I wish to create a new partition for the coming week.
I see an excellent example in the link:
However, I want to implement the partitions to multiple file groups ( With different underlying files). In the article, it is done in Primary file group only
I have few questions:
1) When I merge the 12 week partition with historic partition, Since they are in different file groups how does it actually work. I see that merge is only a meta data operation and not a data movement operation. Can we merge partitions in different partitions.
2) Is there any clean up required since I am intending to merge the 12 week partition with historic partition. What happens to the 12 week file and filegroup.
3) If I want to proceed with the way I explained, Looks like I need to create 1 file and 1 filegroup per every week which I am not interested in. Is there a better way to handle this. Can I place all the 2 months partitions (8 of them)in a single file group. Do you think I will be able to get perf. benifits.
4) Do we need to have each partition in a seperate file group. It may be an ideal case, but difficult to manage. Can you suggest a better way to do this.
Please suggest me any improvements to this approach or share some examples if you have.