SQL Server Data Files Partitioning by Date

One of the thing that we can do to maintain data distribution is by using partitioning on the table. By using partitioning you can partition datafiles according to the partition function which will be mapped to each filegroup. This can be used to maintain data files distribution on drives so we can separate disk drives for certain data which might be used frequently on a specially differentiated fast disk. On this part, I will show you how to setup this partitioning on a table.

1. Create a Sample Partition Database


Here because I haven't had any database to be used for partitioning, I created a new Database called TestPartitioning.
CREATE DATABASE TestPartitioning;

2. Create a Partition Function on the Database


Now we will create partition function that will be used to partition the data according to date. In here I use the date format YYYYMMDD, while any other format can also be used to partition data. Range Right indicating that boundary values will serve as lower bounding and first day of month will be included as part of the rest days of the month. RIGHT means the boundary value will be included to the group to the right of the value, while LEFT means the boundary value will be included to the group to the left of the value.
USE TestPartitioning;
CREATE PARTITION FUNCTION DatePartitionFunction (INT)
AS RANGE RIGHT FOR VALUES 
(20220101, 20220201, 20220301, 20220401, 20220501, 20220601, 
 20220701, 20220801, 20220901, 20221001, 20221101, 20221201);

3. Create File Group Which Will be Different for Each Month

First thing to do is to create file group for each month of the year 2022 to later be used to distribute the data file to each file group.
ALTER DATABASE TestPartitioning
ADD FILEGROUP January2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP February2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP March2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP April2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP May2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP June2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP July2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP August2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP September2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP October2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP November2022;

ALTER DATABASE TestPartitioning
ADD FILEGROUP December2022;

4. Create a File for Each File Group

This is needed so each partition will be stored in different file so later we can maintain housekeeping for each partition.
ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartJan],
    FILENAME = 'C:\Database\DATA\PartitioningDBJan2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [January2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartFeb],
    FILENAME = 'C:\Database\DATA\PartitioningDBFeb2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [February2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartMar],
    FILENAME = 'C:\Database\DATA\PartitioningDBMar2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [March2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartApr],
    FILENAME = 'C:\Database\DATA\PartitioningDBApr2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [April2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartMay],
    FILENAME = 'C:\Database\DATA\PartitioningDBMay2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [May2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartJune],
    FILENAME = 'C:\Database\DATA\PartitioningDBJun2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [June2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartJul],
    FILENAME = 'C:\Database\DATA\PartitioningDBJul2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [July2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartAug],
    FILENAME = 'C:\Database\DATA\PartitioningDBAug2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [August2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartSep],
    FILENAME = 'C:\Database\DATA\PartitioningDBSep2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [September2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartOct],
    FILENAME = 'C:\Database\DATA\PartitioningDBOct2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [October2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartNov],
    FILENAME = 'C:\Database\DATA\PartitioningDBNov2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [November2022];

ALTER DATABASE [TestPartitioning]
    ADD FILE 
    (
    NAME = [PartDec],
    FILENAME = 'C:\Database\DATA\PartitioningDBDec2022.ndf',
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [December2022];

5. Create Partition Scheme for the Partition Function 

You need to create Partition Scheme to Map the Partition Function to each of the filegroup which you have made, make it in order so every filegroup will be mapped accordingly.
CREATE PARTITION SCHEME DatePartitionScheme
AS PARTITION DatePartitionFunction
TO ([January2022], [February2022], [March2022], [April2022], [May2022], [June2022], 
    [July2022], [August2022], [September2022], [October2022], [November2022], [December2022], [PRIMARY]);

6. Create a Table be Used with the Partition Function

Here I created a table and use the SaleDate column as the partition column, if the table already exist you can create new table and export and import data there, also rename the old one to another name and the new one rename to the existing table's name.
USE TestPartitioning;
CREATE TABLE SalesData2022 (
    SaleID INT,
    SaleDate INT,
    Amount DECIMAL(10, 2),
    CONSTRAINT PK_SalesData2022 PRIMARY KEY (SaleID, SaleDate)
)
ON DatePartitionScheme(SaleDate);

7. Insert Data to the Table to Test the Partitioning

In here I insert some data to the table around 10000 rows, which then will be stored according to the partition that we made before.
DECLARE @SaleID INT = 1;

WHILE @SaleID <= 10000
BEGIN
    DECLARE @RandomMonth INT = FLOOR(RAND() * 12) + 1;
    DECLARE @RandomDay INT = FLOOR(RAND() * 28) + 1;
    DECLARE @RandomAmount DECIMAL(10, 2) = ROUND(RAND() * 1000, 2);
    
    DECLARE @SaleDate INT;
    SET @SaleDate = CASE
        WHEN @RandomMonth < 10 THEN CAST('20220' + CAST(@RandomMonth AS VARCHAR(1)) + 
                    RIGHT('0' + CAST(@RandomDay AS VARCHAR(2)), 2) AS INT)
        ELSE CAST('2022' + CAST(@RandomMonth AS VARCHAR(2)) + 
                    RIGHT('0' + CAST(@RandomDay AS VARCHAR(2)), 2) AS INT)
        END;

    INSERT INTO SalesData2022 (SaleID, SaleDate, Amount)
    VALUES (@SaleID, @SaleDate, @RandomAmount);
    
    SET @SaleID = @SaleID + 1;
END;

8. Check the Data Stored if It's Partitioned Correctly or Not

Using Query to system tables we can see how data are distributed in the partitions. If the Rows Counts are there, then the partitioning is working correctly.
WITH PartitionInfo AS (
    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 <= 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;

After all is done then the partitioning has been implemented successfully.

Comments

Post a Comment