CODEWITHSUNDEEP
sql-serverazuredatabase-partitioning
Liam
Liam

Reputation: 493

Azure SQL Monthly Partitioning (Split)

I've used an example from the Microsoft help page to create a partition using YYYYMM on a DateTime2 property (code and link below).

I set the start partition from 201702 (Feb 2017) to 202006 (June 2020).

I want to be able to add (split?) a new partition for the next month, at the beginning of the previous month - if it does not already exist. When the date is 1st June 2020, it will then create a new partition for July. I'm unsure how to proceed.

I've tried looking at the Azure documentation but it only contains an example for a standard number.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15

--Create date partition function with increment by month.  
DECLARE @DatePartitionFunction nvarchar(max) = 
    N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) 
    AS RANGE RIGHT FOR VALUES (';  
DECLARE @i datetime2 = '20170201';  
WHILE @i < '20200601'  
BEGIN  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';  
SET @i = DATEADD(MM, 1, @i);  
END  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';  
EXEC sp_executesql @DatePartitionFunction;  
GO

Upvotes: 1

Views: 1042

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46193

I don't know why they chose to use dynamic SQL in the example but that is not needed. Below is a version with static SQL using a variable to specify the new boundary and includes a partition scheme for completeness.

--Create monthly partitions for all months in the desired range
CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) 
    AS RANGE RIGHT FOR VALUES ();

DECLARE @MonthStartDate datetime2 = '20170201';  
WHILE @MonthStartDate < '20200601'  
BEGIN  
    ALTER PARTITION FUNCTION DatePartitionFunction()
        SPLIT RANGE(@MonthStartDate);
    SET @MonthStartDate = DATEADD(month, 1, @MonthStartDate);
END;

CREATE PARTITION SCHEME DatePartitionScheme
    AS PARTITION DatePartitionFunction ALL TO ([PRIMARY]);
GO

It is best to create new monthly partitions before the start of the next period to avoid costly data movement during SPLIT, which also requires about 4 times the logging compared to normal DML. This example will create a partition for the next month, if it doesn't already exist:

--Run this to create a new partition before the next future month if it doesn't already exist
DECLARE @MonthStartDate datetime2 = DATEADD(day, 1, CAST(EOMONTH(GETDATE()) AS date));  
IF NOT EXISTS(
    SELECT * 
    FROM sys.partition_functions AS pf
    JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
    WHERE
        pf.name = N'DatePartitionFunction'
        AND prv.value = @MonthStartDate
    )
BEGIN
    ALTER PARTITION SCHEME DatePartitionScheme
        NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION DatePartitionFunction()
        SPLIT RANGE(@MonthStartDate);
END;
GO

Upvotes: 2

Related Questions