Partitioning is a powerful feature in SQL Server that can greatly improve the speed and efficiency of your database operations. In this article, we will explore how to use table-level partitioning to move transactional data and optimize delete processes.

Let’s start by creating a sample database to work with:

CREATE DATABASE [PartitionTest1] CONTAINMENT = NONE 
ON PRIMARY ( NAME = N'PartitionTest', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTest.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) 
LOG ON ( NAME = N'PartitionTest_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Next, we’ll add file groups to ensure data file placement flexibility:

--add file groups for future data placement flexibility
ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth01
ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth02
ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth03
--...
ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth12
GO

After adding the file groups, we need to add data files corresponding to each file group:

--add new database data files corresponding to the new file groups
ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month01', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth01.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth01]
--...
ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month12', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth12.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth12]
GO

Now, let’s move on to the partitioning details. We’ll create a function that returns the integer corresponding to the month of a date, a partition function that defines the possible values for partitioning, and a partition scheme that maps these values to the file groups:

USE PartitionTest1
GO

--create function for getting correct partition number from month of date
CREATE FUNCTION [dbo].[fnGetPartition](@Dt Date) RETURNS TINYINT WITH SCHEMABINDING AS 
BEGIN 
    DECLARE @RetVal TINYINT 
    SET @RetVal = datepart(mm, @dt) 
    RETURN @RetVal 
END 
GO

--create possible values for the partitioning function
--notice that 12 will be bundled with the right side of the upcoming partitioning scheme
CREATE PARTITION FUNCTION [pfPartition](TINYINT) AS RANGE LEFT FOR VALUES ( 1,2,3,4,5,6,7,8,9,10,11 )
GO

--create the partition scheme that lines up with the new file groups
CREATE PARTITION SCHEME psPartitionTable AS PARTITION pfPartition TO (FGMonth01,FGMonth02,FGMonth03,FGMonth04,FGMonth05,FGMonth06,FGMonth07,FGMonth08,FGMonth09,FGMonth10,FGMonth11,FGMonth12)
GO

Now, we’ll create two tables: one for the yearly storage of transactional data and another as a staging table for monthly data processing and deletion. These tables should have the same column structure and order:

--create the new test table
CREATE TABLE [dbo].[TestTable1](
    [PartitionNum] AS dbo.fnGetPartition(GeneratedDate) PERSISTED,
    [GeneratedDate] [date] NOT NULL
) ON psPartitionTable(PartitionNum)
GO

--create the staging table to be used for the data deletes. Note: it must be the same
--structure as the source table.
CREATE TABLE [dbo].[StagingTable1](
    [PartitionNum] AS dbo.fnGetPartition(GeneratedDate) PERSISTED,
    [GeneratedDate] [date] NOT NULL
) ON psPartitionTable(PartitionNum)
GO

Now, let’s insert some randomly generated date values into the test table:

--insert 5000 randomly generated dates
INSERT INTO dbo.TestTable1 (GeneratedDate)
SELECT CAST(CAST(GETDATE() AS INT) - 5555 * RAND(CAST(CAST(NEWID() AS BINARY(8)) AS INT)) AS DATETIME)
FROM sys.all_objects
GO 5000

At this point, we can check the partitions and row counts in the test table:

SELECT p.partition_number, fg.name, p.rows
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('dbo.TestTable1')
GO

Now, let’s switch a specific partition from the test table to the staging table. In this example, we’ll switch the partition corresponding to the month of April:

--random month = april (month number 4)
ALTER TABLE dbo.TestTable1 SWITCH PARTITION 4 TO dbo.[StagingTable1] PARTITION 4
GO

After the switch, we can check the partitions and row counts again:

SELECT p.partition_number, fg.name, p.rows
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('dbo.TestTable1')
GO

Finally, we can delete the transactional data from the staging table using a table truncate operation, which is much faster than a delete operation:

TRUNCATE TABLE dbo.StagingTable1
GO

Keep in mind that this example does not consider indexes or primary keys. When applying partitioning to your own environment, make sure to adjust your indexing strategies accordingly.

By utilizing table-level partitioning, you can significantly improve the speed and efficiency of your database operations. Partitioning allows you to divide data based on specific criteria, such as date ranges, and optimize processes like delete operations. This can lead to faster query performance and better overall database management.

Download the full demo script here.

Stay tuned for more SQL Server tips and tricks!