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