Merge
Merge partition will drop the existing partition and merge the partition to the next partition for right range and take the merged last partition boundary value, make sure the partition is empty before merge otherwise both data will merge together.
MSDN: MERGE [ RANGE ( boundary_value) ]
Drops a partition and merges any values that exist in the partition into a remaining partition. RANGE (boundary_value) must be an existing boundary value, of the partition to be dropped.
This argument removes the filegroup that originally held boundary_value from the partition scheme unless a remaining partition uses it, or marks it with the NEXT USED property. The merged partition exists in the filegroup that didn’t hold boundary_value at first. boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including user-defined functions). It can’t reference a Transact-SQL expression. boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column. You also can’t truncate boundary_value during implicit conversion in a way that the size and scale of the value doesn’t match that of its corresponding input_parameter_type.
Note: Split partition affect performance slowness and more logging if we have large data. It will fill the log and data file and take more time to complete. It is always better to plan before and create more empty partition in the initial design itself instead of altering after data inserted. In case if we forgot or any new requirement to SPLIT any of the middle boundary, Switch out the data to staging table and then split the range and Switch IN. Do not do the SPLIT with data in the partition.
Splitting a non-empty partition is an expensive operation, requiring about 4 times the logging compared to DML. Checkout the below links.
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement
Solution
For Normal split, each record is deleted from old partition and then inserted into the new partition.
Make your partition empty before split. That is switch the data to a staging table and split the range then switch data back in.
https://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
Merge carefully, the merge will merge data together, even if we have empty or data in.
It is an expensive operation.
Test Code:
Use the same script used in part 1 for creating database and table.
--Merge
select [Date_Col] , $partition.PF_myDateRange([Date_Col]) as [partition]
from tbl_partition_test
where $partition.PF_myDateRange([Date_Col]) in (1)
-- delete partition 1 Assume it is old data and no longer needes it
--Partition 1 is empty we can merge, For merge we can even merge with data. It will merge to next partition for right range
TRUNCATE TABLE [tbl_partition_test] WITH (PARTITIONS (1));
GO
alter partition function PF_myDateRange() merge range ('2024-01-01 00:00:00.0000000');
-- Now merge with. Merge partition 2 & 3 and see. After merge the boundary value is last partition value Apirl month not the march
/*
[PK_C1810429_tbl_partition_test] CLUSTERED 1 1489 2024-02-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 2 1394 2024-03-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 3 1489 2024-04-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 4 1441 2024-05-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 5 11761 NULL
*/
alter partition function PF_myDateRange() merge range ('2024-04-01 00:00:00.0000000');
/*
[PK_C1810429_tbl_partition_test] CLUSTERED 1 1489 2024-02-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 2 1394 2024-03-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 3 2930 2024-05-01 00:00:00.0000000
[PK_C1810429_tbl_partition_test] CLUSTERED 4 11761 NULL
*/
