Table partitioning is a powerful feature in SQL Server that allows you to divide large tables into smaller, more manageable pieces. This can greatly improve query performance and simplify data management. In this article, we will walk through a simple tutorial on how to horizontally partition a database table.

Step 1: Create New Test Database with Two Different Filegroups

Before we begin, it is recommended to create different filegroups on separate hard disks to take full advantage of partitioning. In this example, we will create a test database with two filegroups – Primary and Secondary.

USE Master;
GO

IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO

CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'C:\Data\Primary\TestDB_Part1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1), 
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'C:\Data\Secondary\TestDB_Part2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1); 
GO

Step 2: Create Partition Range Function

A partition function defines the range of values to be stored in different partitions. In this example, we will assume that the first 10 records are stored in one filegroup and the rest are stored in a different filegroup.

USE TestDB;
GO

CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10);
GO

Step 3: Attach Partition Scheme to FileGroups

The partition function needs to be attached to the filegroups to be used in table partitioning. In this example, we will create a partition on the primary and secondary filegroups.

USE TestDB;
GO

CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO

Step 4: Create Table with Partition Key and Partition Scheme

The table that will be partitioned needs to be created, specifying the column name to be used as the partition key and the partition scheme to partition the table into different filegroups.

USE TestDB;
GO

CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

Step 5: (Optional/Recommended) Create Index on Partitioned Tables

Creating an index on partitioned tables is optional but highly recommended. This can further improve query performance. In this example, we will create a unique clustered index using the same partition scheme and partition key as the partitioned table.

USE TestDB;
GO

CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

Step 6: Insert Data in Partitioned Table

Now, we can insert data into the partitioned table. In this example, we will insert a total of 3 records. The first record with ID equals to 1 will be inserted into partition 1, and the remaining records will be inserted into partition 2.

USE TestDB;
GO

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO

Step 7: Test Data from TestTable

We can now query the partitioned table and see the values that were inserted.

USE TestDB;
GO

SELECT *
FROM TestTable;
GO

Step 8: Verify Rows Inserted in Partitions

We can also query the sys.partitions view to verify that the TestTable contains two partitions, with one record inserted in partition 1 and two records inserted in partition 2.

USE TestDB;
GO

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

Table partitioning is a simple and efficient way to improve performance and manage large tables in SQL Server. In future articles, we will explore more advanced topics related to table partitioning. If you have any questions or need assistance with table partitioning, feel free to reach out to me.