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.