SQL Server – Partitions on Existing Tables

About the fact that my site’s name is Big Data & SQL, I don’t write much regarding SQL. It’s because I don’t want to repeat posts that have been published by many people over the years. There’s almost nothing else to say about SQL at this stage. However, I think this post would undoubtedly assist a few people, since I am assuming it was not well covered previously.

In a database, partitioning divides large tables into many smaller bits. Since the search field is reduced, queries that fetch only a small portion of the data will run quicker. Additionally, the efficiency and response time for specific SQL operations will be improved.

This article focuses on creating the partitions on the existing tables.

Let’s see this exercise by creating a test table.

–Drop the table if already exists.
DROP TABLE IF EXISTS dbo.TestPartition

–Creating the test table
CREATE TABLE dbo.TestPartition(
          StudentID INT PRIMARY KEY,
          StudentName VARCHAR(5) NULL,
          JoinDate DATETIME,
          iYYYYMM INT)
GO

–Sample data insertions
INSERT INTO TestPartition VALUES(101,’SomeA’,’01/05/2021′,202101)
INSERT INTO TestPartition VALUES(102,’SomeB’,’01/05/2021′,202101)
INSERT INTO TestPartition VALUES(103,’SomeC’,’02/05/2021′,202102)
INSERT INTO TestPartition VALUES(104,’SomeD’,’02/05/2021′,202102)
INSERT INTO TestPartition VALUES(105,’SomeE’,’03/05/2021′,202103)
INSERT INTO TestPartition VALUES(106,’SomeF’,’03/05/2021′,202103)
INSERT INTO TestPartition VALUES(107,’SomeG’,’04/05/2021′,202104)
INSERT INTO TestPartition VALUES(108,’SomeH’,’04/05/2021′,202104)

–Verify unpartitioned data from the table “TestPartition”
SELECT
          o.name objectname,
          i.name indexname,
          partition_id,
          partition_number,
          [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE ‘%TestPartition%’



–Creating the partition function
CREATE PARTITION FUNCTION iYYYYMMPF (INT)
AS RANGE RIGHT FOR VALUES (202101, 202102,202103,202104)
GO

–Creating the partition scheme
CREATE PARTITION SCHEME iYYYYMMPS
AS PARTITION iYYYYMMPF ALL TO ([PRIMARY])
GO

–Verify if the partitions are created.
SELECT
        ps.name,
        pf.name,
        boundary_id,
        value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
WHERE ps.name = ‘iYYYYMMPS’

— Drop the primary key constraint and recreate it.
ALTER TABLE dbo.TestPartition
DROP CONSTRAINT PK__TestPart__32C52A799EEAFDF4
GO

ALTER TABLE dbo.TestPartition
ADD CONSTRAINT PK_TestPartition_StdID PRIMARY KEY NONCLUSTERED (StudentID)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

— Create the clustered index on partitioned column.
CREATE CLUSTERED INDEX IX_TestPartition_iYYYYMM ON dbo.TestPartition(iYYYYMM)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON iYYYYMMPS(iYYYYMM)
GO

–Verify the partitioned data.
SELECT
        o.name objectname,
        i.name indexname,
        partition_id,
        partition_number,
        [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE ‘%TestPartition%’

Hope you find this article helpful.

Please do subscribe to receive notifications on latest posts.