SQL Server Automated Partitioning
SQL Server partitioning is a feature that allows you to physically and logically partition data in tables to different files and filegroups. I won’t go through a tutorial of how partitioning works here because that exists in a variety of locations, both online and offline. What I will do is walk you through a solution for automating partitioning from the perspective of adding new partitions, files and filegroups as you move forward in time.
Two general cases for partitioning involve partitioning by week and month. Both are similar although typically monthly partitioning is for archiving data while weekly partitioning is for keeping current data. Often for weekly partitioning data will need to be kept according to a weekday schedule. I have code here for that scenario. Each solution uses a common set of functions and procedures that I’ll detail here.
For these procedures, I create them under a schema called utility, so you will need to create that in your database.
fn_GetFirstDayNextMonth
This function will take a date as a parameter and return a concatenated string value of the next month’s date to pass on to the create partition stored procedure. Typically you will want each month to be represented by a logical partition, so the day value is set to the first day of the month.
CREATE FUNCTION [Utility].[fn_GetFirstDayNextMonth] ( @DateTime DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @DateNextMonth DATETIME;
-- Default to GETDATE if no date is passed
IF @DateTime IS NULL
SET @DateTime = GETDATE();
DECLARE @Month VARCHAR(2);
DECLARE @Year VARCHAR(4);
DECLARE @NextMonth DATETIME;
SET @NextMonth = DATEADD(MONTH, 1, @DateTime);
SET @Month = MONTH(@NextMonth);
SET @Year = YEAR(@NextMonth);
-- Make sure the month is two characters long
IF ( LEN(@Month) = 1 )
SET @Month = '0' + @Month;
-- Get the greatest datetime value that would be assigned to the new partition
SET @DateNextMonth = @Year + @Month + '01';
RETURN (@DateNextMonth);
END;
GO
fn_GetNextWeekDay
This function will take a date as a parameter and return a concatenated string value of the next weekday to pass on to the create partition procedure. This procedure uses the DATEPART function and the WEEKDAY parameter to select the appropriate number of the day. If the day is Friday, it will skip Saturday and Sunday to select Monday. If the day is Saturday, it will skip Sunday to select Monday. The value returned is a string of the year, month and day similar to the above function.
CREATE FUNCTION [utility].[fn_GetNextWeekday] ( @DateTime DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @DateNextWeekday DATETIME;
-- Default to GETDATE if no date is passed
IF @DateTime IS NULL
SET @DateTime = GETDATE();
DECLARE @Month VARCHAR(2);
DECLARE @Year VARCHAR(4);
DECLARE @Day VARCHAR(2);
DECLARE @NextWeekday DATETIME;
DECLARE @WeekDayNumber INT;
SET @WeekDayNumber = DATEPART(WEEKDAY, @DateTime);
IF @WeekDayNumber = 6
SET @NextWeekday = DATEADD(DAY, 3, @DateTime);
ELSE
IF @WeekDayNumber = 7
SET @NextWeekday = DATEADD(DAY, 2, @DateTime);
ELSE
SET @NextWeekday = DATEADD(DAY, 1, @DateTime);
SET @Day = DAY(@NextWeekday);
SET @Month = MONTH(@NextWeekday);
SET @Year = YEAR(@NextWeekday);
-- Make sure the month is two characters long
IF ( LEN(@Month) = 1 )
SET @Month = '0' + @Month;
-- Make sure the day is two characters long
IF ( LEN(@Day) = 1 )
SET @Day = '0' + @Day;
-- Get the greatest datetime value that would be assigned to the new partition
SET @DateNextWeekday = @Year + @Month + @Day;
RETURN (@DateNextWeekday);
END;
GO
fn_GetPrimaryDataFileDirectory
This function simply selects the current directory for the primary data file of the database. Nothing too complicated here.
CREATE FUNCTION [Utility].[fn_GetPrimaryDateFileDirectory] ( )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @PrimaryFileDirectory VARCHAR(MAX);
SELECT @PrimaryFileDirectory = LEFT(physical_name,
LEN(physical_name)
- ( CHARINDEX('',
REVERSE(physical_name),
0) - 1 ))
FROM sys.database_files
WHERE name = DB_NAME();
RETURN (@PrimaryFileDirectory);
END;
GO
sp_AddFileGroup
This procedure adds a filegroup to a database. Logic is wrapped in a TRY..CATCH statement.
BEGIN TRY
SET @strSQL = 'ALTER DATABASE ' + @DatabaseName + ' ADD FILEGROUP ['
+ @FileGroupName + ']';
EXEC(@strSQL);
END TRY
sp_AddDataFileToFileGroup
This procedure adds a file to a filegroup. Logic is wrapped in a TRY..CATCH statement.
BEGIN TRY
SET @strSQL = 'ALTER DATABASE ' + @DatabaseName + '
ADD FILE (Name=''' + @FileName + ''', Filename='''
+ @FullFileName + ''',
Size=' + @FileSize + ', Filegrowth=' + @FileGrowth
+ 'MB) TO FILEGROUP [' + @FileGroupName + ']';
EXEC(@strSQL);
END TRY
sp_AddPartition
This procedure adds a partition by configuring the filegroup in the partitioning scheme and splitting the date range in the partition function. Notice that we use the NEXT USED command to specify the next filegroup to use for the scheme and the SPLIT RANGE command to split the date range, defining a new partition on the previously defined filegroup.
BEGIN TRY
SET @SQLCommand = 'ALTER PARTITION SCHEME ' + @PartitionSchemeName
+ ' NEXT USED [' + @FileGroupName + ']';
EXEC(@SQLCommand);
SET @SQLCommand = 'ALTER PARTITION FUNCTION ' + @PartitionFunctionName
+ '() SPLIT RANGE ('''
+ CONVERT(VARCHAR, @PartitionFunctionDateRange, 21) + ''')';
EXEC(@SQLCommand);
END TRY
sp_MergePartition
This procedure merges two partitions into one by using the MERGE RANGE command of the ALTER PARTITION FUNCTION statement. As before, the command is wrapped in a TRY..CATCH statement.
BEGIN TRY
SET @SQLCommand = N'ALTER PARTITION FUNCTION '
+ @PartitionFunctionName + '()
MERGE RANGE (''' + CONVERT(VARCHAR, @BoundaryValue, 21) + ''')';
EXEC(@SQLCommand);
END TRY
sp_CreateNewPartitionMonthly
We have now finished creating our supporting functions and procedures. This procedure will utilize them to create a new partition on a monthly schedule. Typically, you will call this procedure on a monthly schedule, and it will create a new partition for the following month. It will create any new files in the same directory as the primary file. Furthermore, the procedure is coded to create one file and filegroup per year, so you will have twelve partitions in each filegroup.
The procedure will add a new filegroup, add a new file to the filegroup, add a new partition and merge old partitions. You can adjust the procedure as you see fit. New filegroups and files will only be added in December for the following calendar year.
CREATE PROCEDURE [utility].[sp_CreateNewPartitionMonthly]
@DateTime DATETIME = NULL ,
@DatabaseName VARCHAR(100) ,
@PartitionFunctionName VARCHAR(100) ,
@PartitionSchemeName VARCHAR(100) ,
@AddFileGroupFlag BIT = 1 ,
@AddFileFlag BIT = 1 ,
@AddPartitionFlag BIT = 1 ,
@MergePartitionFlag BIT = 1
AS
DECLARE @FileGroupName VARCHAR(100);
DECLARE @FileName VARCHAR(100);
DECLARE @FullFileName VARCHAR(500);
DECLARE @DateNextMonth DATETIME;
DECLARE @PrimaryFileDirectory VARCHAR(MAX);
DECLARE @BoundaryValue DATETIME;
-- Default to GETDATE if no date is passed
IF @DateTime IS NULL
SET @DateTime = GETDATE();
SET @PrimaryFileDirectory = utility.fn_GetPrimaryFileDirectory();
SET @DateNextMonth = utility.fn_GetFirstDayNextMonth(@DateTime);
SET @FileGroupName = '' + @DatabaseName + '_' + @DateNextMonth;
SET @FileName = '' + @DatabaseName + '_' + @DateNextMonth;
SET @FullFileName = @PrimaryFileDirectory + @FileName + '.ndf';
IF ( @AddFileGroupFlag = 1
AND MONTH(@DateTime) = '12'
)
EXEC utility.sp_AddFileGroup @FileGroupName;
IF ( @AddFileFlag = 1
AND MONTH(@DateTime) = '12'
)
EXEC utility.sp_AddDataFileToFilegroup @DatebaseName, @FileName,
@FullFileName, @FileGroupName;
IF ( @AddPartitionFlag = 1 )
EXEC utility.sp_AddPartition @PartitionFunctionName,
@PartitionSchemeName, @FileGroupName, @DateNextMonth;
-- Default BoundaryValue to the oldest partition
SET @BoundaryValue = ( SELECT TOP 1
CONVERT (DATETIME, value)
FROM sys.partition_range_values
ORDER BY value ASC
);
IF ( @MergePartitionFlag = 1 )
EXEC utility.sp_MergePartition @PartitionFunctionName, @BoundaryValue;
GO
sp_CreateNewPartitionWeekly
The weekly create new partition procedure is similar to the monthly procedure. In this case, the first of the next weekday will be set as the new partition boundary. As before, new files will be added to the same directory as the primary file.
New filegroups and files will be created on December 1st of each year. This is not strictly necessary and can be removed if you desire. New partitions will be created for each weekday and old partitions will be removed based on the last partition available sorted by date ascending.
CREATE PROCEDURE [utility].[sp_CreateNewPartitionWeekday]
@DateTime DATETIME = NULL ,
@DatabaseName VARCHAR(100) ,
@PartitionFunctionName VARCHAR(100) ,
@PartitionSchemeName VARCHAR(100) ,
@AddFileGroupFlag BIT = 1 ,
@AddFileFlag BIT = 1 ,
@AddPartitionFlag BIT = 1 ,
@MergePartitionFlag BIT = 1
AS
DECLARE @FileGroupName VARCHAR(100);
DECLARE @FileName VARCHAR(100);
DECLARE @FullFileName VARCHAR(500);
DECLARE @DateNextWeekday DATETIME;
DECLARE @PrimaryFileDirectory VARCHAR(MAX);
DECLARE @BoundaryValue DATETIME;
-- Default to GETDATE if no date is passed
IF @DateTime IS NULL
SET @DateTime = GETDATE();
SET @PrimaryFileDirectory = utility.fn_GetPrimaryFileDirectory();
SET @DateNextWeekday = utility.fn_GetFirstDayNextWeekday(@DateTime);
SET @FileGroupName = '' + @DatabaseName + '_' + @DateNextWeekday;
SET @FileName = '' + @DatabaseName + '_' + @DateNextWeekday;
SET @FullFileName = @PrimaryFileDirectory + @FileName + '.ndf';
IF ( @AddFileGroupFlag = 1
AND MONTH(@DateTime) = '12'
AND DAY(@DateTime) = '01'
)
EXEC utility.sp_AddFileGroup @FileGroupName;
IF ( @AddFileFlag = 1
AND MONTH(@DateTime) = '12'
AND DAY(@DateTime) = '01'
)
EXEC utility.sp_AddDataFileToFilegroup @DatebaseName, @FileName,
@FullFileName, @FileGroupName;
IF ( @AddPartitionFlag = 1 )
EXEC utility.sp_AddPartition @PartitionFunctionName,
@PartitionSchemeName, @FileGroupName, @DateNextWeekday;
-- Default BoundaryValue to the oldest partition
SET @BoundaryValue = ( SELECT TOP 1
CONVERT (DATETIME, value)
FROM sys.partition_range_values
ORDER BY value ASC
);
IF ( @MergePartitionFlag = 1 )
EXEC utility.sp_MergePartition @PartitionFunctionName, @BoundaryValue;
GO
Once you have your procedures and functions in place, you will have to set up the initial files, filegroups and partitions per your requirements. Since the weekly procedure removes the oldest partition, you can control how many partitions you leave as current from the number of partitions you create to start.
The code for this project can be found here.