As shown in the following diagrams, you can switch oldest data to another table for analysis. This strategy can improve the Customer table query performance.
For the Customer table, the first partition is empty, none of the rows need to move from the first to the second partition, when the merge process runs. If the first partition is not empty and the boundary point is merged, then rows have to move from the first to the second partition, which can be a very expensive operation.
When you will switch out a partition of a table to a nonpartitioned table within the same filegroup, SQL Server can make this switch as a metadata change and no actual data moves.. As a metadata-only change, this can occur within seconds.
Leaving a partition empty at the end will allow you to split the empty partition (for the new data coming in) and not need to move rows from the last partition (because none exist) to the new filegroup that's being added (when the partition is split to include another chunk of data).
However, the Archive table has a very expensive operation when FG1 and FG2 are merged. It is better to assign Partition 1 and Partition 2 to FG1 and FG1 for both Customer Table and Archive table. In this way, only metadata changes and no actual data moves.
---------------
