No, Azure SQL Database does not allow direct modification of a partition function to add more partitions or change existing boundaries. Partition functions in Azure SQL Database (and SQL Server) are static once defined. If you need to add more partitions, you must recreate the partition function and the associated objects (like the partitioned table).

Why Can’t You Modify a Partition Function?

Partition functions define the fixed boundaries that determine how data is distributed across partitions. These boundaries are immutable after the function is created. To add more partitions, you need to define a new partition function with the desired boundaries.

How to Add More Partitions

To add partitions in Azure SQL Database, you must follow these steps:

1. Create a New Partition Function

Define a new partition function with the additional boundaries you need.

Example: Suppose the original partition function was:

CREATE PARTITION FUNCTION myRangePF1 (datetime2)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2022-06-01', '2022-12-01');

To add more partitions for 2023:

CREATE PARTITION FUNCTION myRangePF2 (datetime2)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2022-06-01', '2022-12-01', '2023-06-01', '2023-12-01');

2. Create a New Partition Scheme

Partition schemes map the partition function to the physical storage (filegroups).

Example:

CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
ALL TO ([PRIMARY]); -- Default to the PRIMARY filegroup

3. Recreate the Partitioned Table

You must recreate the table using the new partition function and scheme.

Example:

CREATE TABLE NewPartitionedTable (
    SaleID INT,
    SaleDate DATETIME2,
    Amount DECIMAL(10, 2)
) ON myRangePS2(SaleDate);

4. Migrate Data

Move the data from the old partitioned table to the new one.

Example:

INSERT INTO NewPartitionedTable (SaleID, SaleDate, Amount)
SELECT SaleID, SaleDate, Amount
FROM OldPartitionedTable;

5. Drop the Old Table

After verifying the migration, drop the old table.

Example:

DROP TABLE OldPartitionedTable;

Limitations to Keep in Mind

1. Downtime: The process of recreating the table and migrating data may require downtime, especially for large tables.
2. Resource-Intensive: Data migration for large datasets can be resource-intensive, so plan during low-traffic periods.
3. Indexes and Constraints: You need to recreate indexes, constraints, and any other associated objects on the new table.

Best Practices for Partition Growth

1. Plan for Future Growth:
   • Define a partition function with additional boundaries to accommodate future data (e.g., partitions for the next 5–10 years).
   • Example:

CREATE PARTITION FUNCTION myRangePF1 (datetime2)
AS RANGE RIGHT FOR VALUES (
    '2022-01-01', '2022-06-01', '2022-12-01', 
    '2023-06-01', '2023-12-01', '2024-06-01'
);

2.Monitor Partition Usage:

• Regularly check the number of partitions and ensure you don’t approach the 15,000 partition limit.

3.Archive Old Data:

• Archive data from older partitions to free up space for new partitions.

Summary

You cannot directly modify a partition function in Azure SQL Database to add more partitions. Instead, you must recreate the partition function, partition scheme, and partitioned table, then migrate the data. To avoid frequent changes, design your partition function to anticipate future data growth.

Mohammad Gufran Jahangir