In SQL Server, you can use the ALTER PARTITION FUNCTION
to merge two partitions into one partition.
To do this, use the MERGE RANGE
argument, while providing the boundary value of the partition to drop.
This operation drops the partition and merges any values that exist in the partition into a remaining partition.
Example
Imagine we have five partitions that we want to become four.
Current Partitions
We have a partition function called MoviesPartitionFunction
with four boundary values.
SELECT
prv.boundary_id,
prv.[value]
FROM sys.partition_range_values prv
INNER JOIN sys.partition_functions pf
ON prv.function_id = pf.function_id
WHERE pf.name = 'MoviesPartitionFunction';
Result:
+---------------+---------+ | boundary_id | value | |---------------+---------| | 1 | -1 | | 2 | 100 | | 3 | 500 | | 4 | 10000 | +---------------+---------+
This means there are five partitions.
For this example, we’ll drop the boundary range 500.
Also, imagine we already have a table with data distributed across some of these partitions.
Here’s how the rows are currently distributed across the partitions.
SELECT
partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.Movies');
Result:
+--------------------+-------------+ | partition_number | row_count | |--------------------+-------------| | 1 | 0 | | 2 | 100 | | 3 | 400 | | 4 | 3579 | | 5 | 0 | +--------------------+-------------+
I should point out that Microsoft actually advises against merging (or splitting) partitions that contain data. However, for the purpose of this example, we’ll throw caution to the wind and merge two partitions that contain data.
Merge the Partitions
OK, let’s merge partitions.
ALTER PARTITION FUNCTION MoviesPartitionFunction()
MERGE RANGE (500);
Result:
Commands completed successfully.
We successfully merged the partitions.
Check the Result
Let’s check the result.
SELECT
prv.boundary_id,
prv.[value]
FROM sys.partition_range_values prv
INNER JOIN sys.partition_functions pf
ON prv.function_id = pf.function_id
WHERE pf.name = 'MoviesPartitionFunction';
Result:
+---------------+---------+ | boundary_id | value | |---------------+---------| | 1 | -1 | | 2 | 100 | | 3 | 10000 | +---------------+---------+
As expected, the boundary range of 500 has been dropped, and we’re left with just three boundary ranges.
Let’s see how the data is distributed across the partitions.
SELECT
partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.Movies');
Result:
+--------------------+-------------+ | partition_number | row_count | |--------------------+-------------| | 1 | 0 | | 2 | 100 | | 3 | 3979 | | 4 | 0 | +--------------------+-------------+
Again as expected, the data from partitions 3 and 4 have merged into one partition (partition 3).
Why Have Empty Partitions?
If you’re wondering why I have empty partitions at each end, this is done specifically to facilitate splitting and merging of partitions.
Keeping partitions empty on each end prevents any unexpected data movement you might get when splitting or merging partitions.
This practice is also recommended by Microsoft, for precisely this reason.
Merging Partitions that Contain Data
As mentioned, Microsoft recommends against merging partitions that already contain data.
Splitting or merging populated partitions can be inefficient. They can be inefficient because the split or merge may cause as much as four times more log generation, and may also cause severe locking.