If you have a partitioned table or index in SQL Server, but you need more partitions, you can add a partition to the partition function using the ALTER PARTITION FUNCTION statement with the SPLIT RANGE argument.

When you do this, you split an existing partition into two.

Example

Here’s an example to demonstrate. First, let’s look at our current setup.

Current Setup

We already have four partitions, and we want to add a fifth.

So we have already created a partition function like this:

CREATE PARTITION FUNCTION MoviesPartitionFunction (int)  
    AS RANGE LEFT FOR VALUES (-1, 100, 10000);

This code results in partitions that store values as follows.

Partition	Values
1	<= –1
2	> –1 AND <= 100
3	> 100 AND <=10000
4	> 10000

For this example, we’ll be adding a new boundary value of 500.

So we want it to look like this:

Partition	Values
1	<= –1
2	> –1 AND <= 100
3	> 100 AND <=500
4	> 500 AND <=10000
5	> 10000

Also, for the purpose of this example, imagine we have table that is partitioned using the above partition function, and it currently contains just over four thousand rows of data.

Let’s take a quick look at how the rows are distributed across our 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           |
+--------------------+-------------+

So we now want to add a new partition with a boundary value of 500.

In this case, we will split the partition between boundary values 100 and 10000.

I should mention that Microsoft recommends against splitting populated partitions. So that’s something to keep in mind.

However, for the purpose of this example, we’ll split a partition that contains data.

Split the Partition

Here’s where we go ahead and split the partition.

We use the ALTER PARTITION FUNCTION statement to split the partition.

However, splitting a partition creates two partitions out of one, and so we need to make sure there’s a filegroup for the new partition. We also need to make sure that our partition scheme knows which filegroup to use when we split the partition.

You can use an existing filegroup, or you can create a new one.

Let’s create a new one.

Here’s the code we can use to do all of the above:

ALTER DATABASE Test ADD FILEGROUP MoviesFg5;

ALTER DATABASE Test   
ADD FILE   
(  
    NAME = MoviesFg5dat,  
    FILENAME = '/var/opt/mssql/data/MoviesFg5dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP MoviesFg5;

ALTER PARTITION SCHEME MoviesPartitionScheme  
NEXT USED MoviesFg5;

ALTER PARTITION FUNCTION MoviesPartitionFunction()
SPLIT RANGE (500);

Result:

Commands completed successfully.

We have successfully split the partition.

Verify the Split

We can now verify that the partition function has been modified to reflect the new 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   |
+---------------+---------+

So we can see that the new boundary value has been successfully added.

And here’s how the data is now 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           |
+--------------------+-------------+

So we now have 5 partitions.

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.

Splitting Partitions that Contain Data

As mentioned, Microsoft recommends against splitting 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.