This is really awesome !! You can split a partition (filegroup) with data without resulting in any data movement.

Imagine a partitioned table with partitions for each month of a calendar year going to a dedicated file group. Each month data goes into its own partition and as you move forward, the sliding window opens up empty partitions on one end, and removes (and merges) old partitions on the other.

Table Partition & FileGroups

Now for some reason, if you do not run the monthly sliding window, the new month’s data starts accumulating in the same partition (and filegroup) as previous month partition (and filegroup). Essentially data from two different months goes into the same partition.

When you introduce a partition, for current month, it results in a lot of data movement. Current months data that is stored in the previous months data needs to be moved to it’s own partition. This I/O movement may not always be a welcome activity on VLDB’s.

So, you have two options now:

1. Create new partition for starting of the month or
2. Create new partition on future date; Something like a few hours in future, to prevent any data movement.

Obviously, Option 1 results in a lot of data movement, but to my surprise option 2 also results in data movement. In option 2, though we create a new partition in future date, Sql Server goes through the old partition to be sure that there is no data that need’s to be moved. Sometime this could take a long, long time.

But there is a new option that results in zero data movement. Microsoft folks have suggested this new way to partition table with no data movement.

Follow these steps:

1. Create archive table that is also partitioned on the same partition function and partition scheme.
2. On the main table, Switch out the partition with data for more than one month to the archive table, with an empty partition from archive table. No data movement.
3. Now, the main table has two empty partition on the end.
4. Since it’s empty, we can SPLIT it with today’s date. No data movement.
5. Now Switch back the partition from archive table to main table’s penultimate partition. No data movement again.

Now we created new partition on main table without any data movement.

Voila !! no data movement at all.

Hope this helps,
_Sqltimes