DEV Community

jeevan wijerathna
jeevan wijerathna

Posted on • Originally published at iamjeevan.com

SQL Server Database Partitioning

#sql #sqlserver

What is database table partitioning?

By Default Data of a table reside in one filegroup called Primary. Partitioning enables divide large table into units that may be spread across more than one filegroup in a database and offer fast ways to load and remove large amounts of data from a table. By splitting into smaller units, queries that access only a fraction of the data can run faster because there is less data to scan.

Benefits of Partitioning

  • Aid in Maintenance of Large Table

Perform maintenance operations quickly because operations target only subsets of data instead of the whole table.

We can transfer or access subsets of data quickly and efficiently while maintaining the integrity of a data collection.

Ex.
Loading data to the table (ETL)

  • Reduce overall response time to read and load data

Queries may be improved as the query might only scan a specific partition or partitions instead of the entire Table.

Types of Partition

  1. Vertical Partition

The table will be divided into multiple Tables based on columns.

  1. Horizontal Partition

The Table will be divided into multiple with the same number of Columns with a fewer number of rows.

Create Horizontal partitioning in SQL server.

  1. Create Test DB and Test Table 
   CREATE DATABASE PartitionTest

   GO

   USE PartitionTest

   CREATE TABLE Orders
       (
         OrderID INT IDENTITY NOT NULL,
         OrderDate DATETIME NOT NULL ,
         OrderMonth INT NOT NULL
       );
Enter fullscreen mode Exit fullscreen mode
  1. Create the partition function

Create Partition function. Defines the number of Partitions. We need to have a column(Partition Column) of Table or index that will be used by partition functions to create and populate partitions. Each value in the partitioning column is an input to the partitioning function, which returns a partition value.

Partitioning Column (OrderMonth)

   CREATE PARTITION FUNCTION PartitionByMonth (INT)
   AS RANGE RIGHT
   FOR VALUES (202201, 202202, 202203);
Enter fullscreen mode Exit fullscreen mode
  1. Create File Group 
   --Create File Group
   ALTER DATABASE PartitionTest ADD FILEGROUP FGJan
   GO
   ALTER DATABASE PartitionTest ADD FILEGROUP FGFeb
   GO
   ALTER DATABASE PartitionTest ADD FILEGROUP FGMarch
   GO
Enter fullscreen mode Exit fullscreen mode
  1. Add files to the filegroups 
   --Create files and attach to File Group
   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_Jan],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Jan.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGJan

   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_Feb],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Feb.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGFeb

   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_March],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_March.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGMarch
Enter fullscreen mode Exit fullscreen mode
  1. Create the partition scheme 
   --Create the partition scheme
   CREATE PARTITION SCHEME OrdersPS
    AS PARTITION PartitionByMonth
    TO ([Primary], FGJan, FGFeb, FGMarch);
Enter fullscreen mode Exit fullscreen mode

Here we have to mention all the filegroups including default Primary File Group.

Query to get available File Groups

   SELECT name AS AvailableFilegroups
     FROM sys.filegroups
     WHERE type = 'FG'
Enter fullscreen mode Exit fullscreen mode
  1. Create/Update Table with Partitioning 
   --Create Index
   CREATE CLUSTERED INDEX IX_Orders ON [dbo]. [Orders]
   (
      [OrderMonth]
   )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON OrdersPS(OrderMonth)
Enter fullscreen mode Exit fullscreen mode
  1. Verify partitions with Row Count 
   SELECT p.partition_number AS PartitionNumber,
          f.name             AS PartitionFilegroup,
          p.rows             AS NumberOfRows
   FROM sys.partitions p
            JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
            JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
   WHERE OBJECT_NAME(OBJECT_ID) = 'Orders'
Enter fullscreen mode Exit fullscreen mode

| PartitionFilegroup | PartitionNumber | NumberOfRows |
| ------------------ | --------------- | ------------ |
| PRIMARY | 1 | 0 |
| FGJan | 2 | 0 |
| FGFeb | 3 | 0 |
| FGMarch | 4 | 0 |

Insert Test Data

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-01-18 17:25:05.000', 202201);

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-02-18 17:25:39.000', 202202);

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-03-18 17:25:58.000', 202203);
Enter fullscreen mode Exit fullscreen mode

We can see rows are distributed among each file groups

| PartitionFilegroup | PartitionNumber | NumberOfRows |
| ------------------ | --------------- | ------------ |
| PRIMARY | 1 | 0 |
| FGJan | 2 | 1 |
| FGFeb | 3 | 1 |
| FGMarch | 4 | 1 |

Complete Query 

CREATE DATABASE PartitionTest

GO

USE PartitionTest

CREATE TABLE Orders
(
    OrderID    INT IDENTITY NOT NULL,
    OrderDate  DATETIME     NOT NULL,
    OrderMonth INT          NOT NULL
);

CREATE PARTITION FUNCTION PartitionByMonth (INT)
    AS RANGE RIGHT
    FOR VALUES (202201, 202202, 202203);

--Create File Group
ALTER DATABASE PartitionTest ADD FILEGROUP FGJan
GO
ALTER DATABASE PartitionTest ADD FILEGROUP FGFeb
GO
ALTER DATABASE PartitionTest ADD FILEGROUP FGMarch
GO
--Create files and attach to File Group
ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_Jan],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Jan.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGJan

ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_Feb],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Feb.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGFeb

ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_March],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_March.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGMarch

GO

--Create the partition scheme
CREATE PARTITION SCHEME OrdersPS
    AS PARTITION PartitionByMonth
    TO ([Primary],FGJan, FGFeb, FGMarch);

--Create Index
CREATE CLUSTERED INDEX IX_Orders ON [dbo].[Orders]
    (
     [OrderMonth]
        ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON OrdersPS(OrderMonth)

-- Insert Test Data
INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-01-18 17:25:05.000', 202201);

INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-02-18 17:25:39.000', 202202);

INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-03-18 17:25:58.000', 202203);

-- View File Groups
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'

-- View Row counts of Partitions
SELECT p.partition_number AS PartitionNumber,
       f.name             AS PartitionFilegroup,
       p.rows             AS NumberOfRows
FROM sys.partitions p
         JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
         JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'Orders'
Enter fullscreen mode Exit fullscreen mode

References

Top comments (0)

Read next

mr_destructive profile image

Build Golang from Source for v1.23+

Meet Rajesh Gor -

ivanivanovv profile image

Launching Vratix - open source Node.js API modules

Ivan Ivanov -

lucaargentieri profile image

What is the ideal workflow between the design and frontend team when you have tight deadlines?

Luca Argentieri -

peterdanwan profile image

Contributing to expressjs

Peter Wan -