Extending SQL Server Data Partition

When you apply a partition to SQL Server table you might have a question, what if the partition that was created is reaching its limit? And what if it's already past the limit and new data keeps on coming? The answer is simple, if there is no more partition for the data then all the new data outside of the partition range will be placed at the PRIMARY filegroup which was created at default by SQL Server when a Database is created or it can be dumped all on the latest partition.

So then, how to extend a partition when it's already at limit or even nearing its limit? On this part, we will see how to extend a partition from an already partitioned table. Partition usually is made by Date, which can be an Int data type or Date data type.

To setup a partitioned table, follow the previous guide on setting partition on SQL Server: SQL Server Setup Partition

1. See the current partition and the column which the partition is based upon

Firstly, we need to know how is the current partition status on the table and the partition column that the table used, so we can extend it with the same format.

On the table, right-click and script it out with CREATE TO and you will have new query that shows you which column is the partition column. You might also want to see the column data to know how it looks like.

   SELECT 
        p.partition_number AS PartitionNumber,
        ps.name AS PartitionScheme,
        pf.name AS PartitionFunction,
        prv.value AS RangeBoundary
    FROM 
        sys.partitions p
        INNER JOIN sys.objects o ON p.object_id = o.object_id
        INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
        INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
        INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
        LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id + 1
    WHERE 
        o.name = 'SalesData2022'  -- Replace with your table name
        AND i.index_id &lt;= 1  -- 0 for heap, 1 for clustered index
)
SELECT 
    pi.PartitionNumber,
    pi.PartitionScheme,
    pi.PartitionFunction,
    pi.RangeBoundary,
    COUNT(s.SaleID) AS RowsInPartition
FROM 
    PartitionInfo pi
    LEFT JOIN SalesData2022 s 
    ON $PARTITION.DatePartitionFunction(s.SaleDate) = pi.PartitionNumber
GROUP BY 
    pi.PartitionNumber,
    pi.PartitionScheme,
    pi.PartitionFunction,
    pi.RangeBoundary
ORDER BY 
    pi.PartitionNumber;

Use this query to see what is the partition limit for each range of each partition.

You can see that I have a table that has partition for data from January 2022 until December 2022, now I will extend the partition for another year data of 2023. (even if the table is called SalesData2022, for this example purpose I will just extend it to this table)

2. Create new FileGroup for the next partitions

So what we need to do is actually like when we want to create a new partition, we need to create new filegroups on which the data will be stored into.

ALTER DATABASE TestPartitioning
ADD FILEGROUP January2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP February2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP March2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP April2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP May2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP June2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP July2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP August2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP September2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP October2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP November2023;

ALTER DATABASE TestPartitioning
ADD FILEGROUP December2023;

By using the SQL Query above I created 12 new filegroups which will host my new 2023 data into each partition per month.

3. Create new data files for each new partition filegroup

Next thing to do is to create new physical data file to store the data for each filegroup.

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Jan],
    FILENAME = 'D:\Database\DATA\PartitioningDBJan2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [January2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Feb],
    FILENAME = 'D:\Database\DATA\PartitioningDBFeb2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [February2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Mar],
    FILENAME = 'D:\Database\DATA\PartitioningDBMar2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [March2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Apr],
    FILENAME = 'D:\Database\DATA\PartitioningDBApr2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [April2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023May],
    FILENAME = 'D:\Database\DATA\PartitioningDBMay2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [May2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023June],
    FILENAME = 'D:\Database\DATA\PartitioningDBJun2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [June2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Jul],
    FILENAME = 'D:\Database\DATA\PartitioningDBJul2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [July2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Aug],
    FILENAME = 'D:\Database\DATA\PartitioningDBAug2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [August2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Sep],
    FILENAME = 'D:\Database\DATA\PartitioningDBSep2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [September2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Oct],
    FILENAME = 'D:\Database\DATA\PartitioningDBOct2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [October2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Nov],
    FILENAME = 'D:\Database\DATA\PartitioningDBNov2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [November2023];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [Part2023Dec],
    FILENAME = 'D:\Database\DATA\PartitioningDBDec2023.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [December2023];

I use the SQL query above to create new data file for each partition filegroup.

4. Alter Partition and Assign new Range Boundary for the new partition

Now what we are doing is to assign the partition scheme next use partition to each of the partition filegroup. For each next use partition, we split the partition according to the range of the data in the column which is used as the range boundary of each partition.

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED January2023; --this is the filegroup name
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230101'); --this is the range boundary

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED February2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230201');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED March2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230301');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED April2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230401');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED May2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230501');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED June2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230601');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED July2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230701');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED August2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230801');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED September2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20230901');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED October2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20231001');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED November2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20231101');

ALTER PARTITION SCHEME DatePartitionScheme
NEXT USED December2023;
ALTER PARTITION FUNCTION DatePartitionFunction()
    SPLIT RANGE ('20231201');

Notes: You need to be careful when defining the alter partition scheme, define the split range carefully and don't be mistaken because it can lead to data loss if you need to switch back partition or move back data if not careful. Do each of partition one at a time if you need to.

You can add the new partition before or after new data are inserted into the table.

5. Check the new partition and new boundary

Use the query in the first step to check on the partition information of the table again.

This time my table now has 2023 partition with new data in each partitions. Because I already inserted new data to the table before the partitions are made, it is automatically distributed by SQL Server when I created the partitions after.

And that's all of it to extend partitions on table when it is nearing its limit or already reaching limit of the partition range.

Search This Blog

Ryzora's Blog