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%’

SQL_Partitioning_Data

–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’

DataInPartitions_1

— 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%’

DataInPartitions_2

Hope you find this article helpful.

Please do subscribe to receive notifications on latest posts.

5 comments

  1. I think that is among the so much vital info for me. And i’m happy
    reading your article. However should commentary on few
    general issues, The site taste is wonderful, the articles is in point of fact nice : D.
    Good job, cheers

    Like

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers. Your feedback is really valuable to me. Thanks a lot.

      Like

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers. Your feedback is really valuable to me. Thanks a lot.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s