SQL Server Partitioning Made Simple: Step-by-Step for Beginners

Hey friends, today I show you step-by-step for "Table Partitioning by Range" in SQL Server.

Imagine you have a HUGE table of all your sales, from many years. Finding one year's sales is slow! So we need to do "Table Partitioning", means we break this one huge list into smaller lists, like making a separate list for each year.

Why Do Table Partitioning?

Faster Searches:

If you only want 2023 sales, the computer only looks in the 2023 "house" – much faster than checking the whole huge list!

Easier Cleanup:

If 2022 data is very old, you can quickly remove or archive its entire "house" without touching other years.

Better Management:

You can put different years on different types of disk drives (e.g., fast disk for current year, slow disk for old years).

Learn How to Boost MySQL Performance: A Simple Guide to Database Partitioning by List

Step 1: Create Database and filegroup for Each Year's (check video for detail)

CREATE DATABASE SalesDB_PTT;

ALTER DATABASE SalesDB_PTT ADD FILEGROUP FG_Sales_2022;
ALTER DATABASE SalesDB_PTT ADD FILEGROUP FG_Sales_2023;
ALTER DATABASE SalesDB_PTT ADD FILEGROUP FG_Sales_2024;
ALTER DATABASE SalesDB_PTT ADD FILEGROUP FG_Sales_2025;

Learn More Introduction to Database (SQL) in Khmer with videos: Chapter 1 | Chapter 2 | Chapter 3

Step 2: Add Data Files to Filegroups

ALTER DATABASE SalesDB_PTT 
ADD FILE 
	(NAME = SalesData_2022, FILENAME = 'D:\DB_DATA\SalesData_2022.ndf', SIZE = 100MB, 
	 MAXSIZE = UNLIMITED, FILEGROWTH = 50MB) 
TO FILEGROUP FG_Sales_2022;

ALTER DATABASE SalesDB_PTT 
ADD FILE 
	(NAME = SalesData_2023, FILENAME = 'D:\DB_DATA\SalesData_2023.ndf', SIZE = 100MB, 
	 MAXSIZE = UNLIMITED, FILEGROWTH = 50MB) 
TO FILEGROUP FG_Sales_2023;

ALTER DATABASE SalesDB_PTT 
ADD FILE 
	(NAME = SalesData_2024, FILENAME = 'D:\DB_DATA\SalesData_2024.ndf', SIZE = 100MB, 
	MAXSIZE = UNLIMITED, FILEGROWTH = 50MB) 
TO FILEGROUP FG_Sales_2024;

ALTER DATABASE SalesDB_PTT 
ADD FILE 
	(NAME = SalesData_2025, FILENAME = 'D:\DB_DATA\SalesData_2025.ndf', SIZE = 100MB, 
	MAXSIZE = UNLIMITED, FILEGROWTH = 50MB) 
	
TO FILEGROUP FG_Sales_2025;
GO;

Important: You need to tell the computer the real path, Create a folder in "D:\DB_DATA" for example.

Step 3: Tell Computer the "Rules" for Breaking Apart (Partition Function)

USE SalesDB_PTT;
GO


CREATE PARTITION FUNCTION pf_SalesByYear (DATE)
AS RANGE LEFT FOR VALUES 
('2022-12-31', '2023-12-31', '2024-12-31');

This mean:
* Sales before Jan 1, 2023, go into the first part.
* Sales from Jan 1, 2023, up to Dec 31, 2023, go into the second part.
* Sales from Jan 1, 2024, up to Dec 31, 2024, go into the third part.
* Sales from Jan 1, 2025, and onwards, go into the fourth part.

Step 4: Make a "Map" (Partition Scheme)

Create a Partition Scheme (maping partition to file group)

CREATE PARTITION SCHEME ps_SalesByYear
AS PARTITION pf_SalesByYear
TO (FG_Sales_2022, FG_Sales_2023, FG_Sales_2024, FG_Sales_2025);
GO

* It says:
* The first part of data (from the rules) goes into `FG_Sales_2022`.
* The second part goes into `FG_Sales_2023`.
* The third part goes into `FG_Sales_2024`.
* The fourth part goes into `FG_Sales_2025`.

Step 5: Create Your "Big List" (Table)

Now, you create your actual `Sales` table.

CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) NOT NULL,
    OrderDate DATE NOT NULL,
	CustomerID INT,
    Amount DECIMAL(10,2),
	PRIMARY KEY CLUSTERED (SaleID, OrderDate)
)
ON ps_SalesByYear(OrderDate);

Step 6: Add Your Sales Data

You insert all your sales data into the `Sales` table. Below is an example of insert data for 2022 (10,000 rows) - you can just change the year and insert another data.

INSERT INTO Sales (OrderDate, CustomerID, Amount)
SELECT Top 10000
    DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '2022-01-01'),
    ABS(CHECKSUM(NEWID())) % 1000 + 1,
    CAST(RAND(CHECKSUM(NEWID())) * 1000 AS DECIMAL(10, 2))
FROM sys.objects o1, sys.objects o2;

Step 7: Verify Partitioning

SELECT
    p.partition_number,
    f.name AS filegroup_name,
    p.rows,
    CAST(p.data_compression_desc AS NVARCHAR(50)) AS data_compression
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
JOIN sys.filegroups f ON au.data_space_id = f.data_space_id
WHERE p.object_id = OBJECT_ID('Sales')
ORDER BY p.partition_number;

For data selection with different examples let check in video for detail.

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming