Horizontal Partitioning on SQL Server with existing data.

poc.sql

-- 1. Create a sample database and tables
CREATE DATABASE TestDB; 
USE TestDB
CREATE TABLE EmployeeReports
(
  ReportID int IDENTITY (1,1) NOT NULL,
  ReportName varchar (100),
  ReportNumber varchar (20),
  ReportDescription varchar (max),
  ReportDate bigint
  CONSTRAINT EReport_PK PRIMARY KEY CLUSTERED (ReportID)
);

-- 2. Fill up the database with sample data 
DECLARE @i int
SET @i = 1
 
BEGIN TRAN
  WHILE @i < 100000 
    BEGIN
      INSERT INTO EmployeeReports (ReportName, ReportNumber, ReportDescription, ReportDate)
      VALUES ('ReportName', CONVERT (varchar (20), @i), REPLICATE ('Report', 1000), CAST(RAND(CHECKSUM(NEWID()))*94694400000 as bigint) + 1509459148000)
      
      SET @i=@i+1
    END

COMMIT TRAN;
                                                                   
-- 3. Create the first partition by determining the oldest boundary. 
-- DECLARE @DatePartitioningFunction nvarchar(max) = 

CREATE PARTITION FUNCTION ReportsRetention (BIGINT) AS 
	RANGE LEFT FOR VALUES(1546261199999);

-- 4. Create partition scheme. First, we map all the partitions to the primary filegroup. 
CREATE PARTITION SCHEME partition_scheme_reports
AS PARTITION ReportsRetention ALL TO ([PRIMARY]);
								   
-- 5. Create partitioned table.
CREATE TABLE PartitionEmployeeReports (
  ReportID int IDENTITY (1,1) NOT NULL,
  ReportName varchar (100),
  ReportNumber varchar (20),
  ReportDescription varchar (max), 
  ReportDate bigint
  CONSTRAINT P_EReport_PK PRIMARY KEY CLUSTERED (ReportID, ReportDate)
)
ON partition_scheme_reports(ReportDate);
								   
-- 6. Transfer data from unpartitioned table to partitioned table 
SET IDENTITY_INSERT PartitionEmployeeReports ON -- This we use to maintain assigned identities. 
INSERT INTO PartitionEmployeeReports (ReportID, ReportName, ReportNumber, ReportDescription, ReportDate)
SELECT ReportID, ReportName, ReportNumber, ReportDescription, ReportDate
FROM EmployeeReports;	
								   
-- 7. OPTIONAL: Verify that data is partitioned
SELECT * 
FROM sys.partitions P 
JOIN sys.objects O 
ON P.object_id = O.object_id
WHERE O.name = 'PartitionEmployeeReports'
								   
-- IMPLEMENTING A SLIDING WINDOW APPROACH --

-- 8. Creating a Target table, which mirrors the structure of the partitioned (source) table 
CREATE TABLE PartitionEmployeeReports_STAGING (
  ReportID int IDENTITY (1,1) NOT NULL,
  ReportName varchar (100),
  ReportNumber varchar (20),
  ReportDescription varchar (max), 
  ReportDate bigint
  CONSTRAINT P_EReport_PK_STAGING PRIMARY KEY CLUSTERED (ReportID, ReportDate)
)
ON partition_scheme_reports(ReportDate);
								   
-- 9. Switch partition from source table to the target table 
ALTER TABLE PartitionEmployeeReports SWITCH PARTITION 1 TO PartitionEmployeeReports_STAGING PARTITION 1
								   
-- 10. Now we can safely truncate the target table 
TRUNCATE TABLE PartitionEmployeeReports_STAGING
								   
-- 11. As a next step, we need to split the partition to accomodate for the new boundary. 
ALTER PARTITION FUNCTION ReportsRetention() SPLIT RANGE(1572524797000)
								   
-- 12. Merge old partition into the new partition 
ALTER PARTITION FUNCTION ReportsRetention() MERGE RANGE(1546261199999)
								   
-- 13. Automate!