Split partition function increased the size of the mdf file.

Question

I had two partitions for a table. For one of the partitions I further split it into two partitions. This caused increase in the size of the mdf file. Why would the split of the partition increase the size of the mdf though there is no data added?
After splitting partition, I have first, second and third partition, the first being the oldest data and the third being the latest data. I merged the second partition with the first one which exists in different filegroup. But even after merging the file size of the ndf file of the first partition where now the second is also merged is not increased. I expected this file size to increase.

Answer 1

ALTER PARTITION SCHEME PartitionSchemeCommissionHistoryByRowId NEXT USED [PRIMARY];  
ALTER PARTITION FUNCTION PartitionFuncCommissionHistoryByRowId()SPLIT RANGE(43315513);  

The above statements created partition 3 on the PRIMARY filegroup and moved all rows with values >= 43315513 from the second partition, also on PRIMARY, to the new third partition. Additional space was needed because the same rows were present in both second and third partitions until the SPLIT operation completed and committed. The mdf file grew during the operation because the mdf file apparently didn't have enough space to accommodate both sets of rows side-by-side. That extra space became unused after the SPLIT completed.

ALTER PARTITION SCHEME PartitionSchemeCommissionHistoryByRowId NEXT USED SECONDARY;  
ALTER PARTITION FUNCTION PartitionFuncCommissionHistoryByRowId()MERGE RANGE(1701149);  

The NEXT USED specification was not used by MERGE because no new partition was created. All rows from the second partition (on PRIMARY) are moved to the existing first partition (on SECONDARY). Since the SECONDARY ndf file didn't increase it size, it seems that filegroup already had enough unused space to accommodate the moved rows without growing.

Additional answers to questions in comments:

Was there a way I could have done to avoid the expansion of file on primary?

The easiest way is to SPLIT the function before inserting rows greater than or equal to the new boundary. No data needs to be moved in this case so the operation will be fast and not require additional space. This requires planning in advance according to your partitioning scenario.

Can I reduce the size of the file since it has expanded to the size which is not needed?

You can release unused space with DBCC SHRINKFILE. If you specify only the file name (e.g. DBCC SHRINKFILE('YourDataFile');), the file size will be reduced to the original size (if possible) by moving used pages from the end of the file as necessary and releasing space of unallocated pages from the end of the file. Be aware that this can introduce fragmentation, which is a concern mostly with spinning media. You can avoid fragmentation by first rebuilding clustered index on the second partition and then shrinking to the desired size with the TRUNCATEONLY option (e.g. DBCC SHRINKFILE('YourDatabase', 10000, TRUNCATEONLY);).

Answer 2

I assume you had non-empty partition, so splitting a non-empty partition is an expensive operation, requiring about 4 times the logging compared to DML.