Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Partitioning splits one table into separate partitions by the value of one column, usually a date. Its real benefits are management ones: removing a month of old data in seconds, rebuilding one partition instead of the whole table, and placing old data on cheaper storage. It is not a performance fix by itself, and queries only benefit when they filter on the partitioning column. It is available in all editions from SQL Server 2016 SP1.

Step 1: Partition function and scheme

USE [YourDB];
-- RANGE RIGHT: each boundary is the first day of its partition
CREATE PARTITION FUNCTION pf_OrderDate (date)
AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate ALL TO ([PRIMARY]);

Step 2: Create the table on the scheme

CREATE TABLE dbo.Orders (
    OrderID    bigint        NOT NULL,
    OrderDate  date          NOT NULL,
    CustomerID int           NOT NULL,
    Amount     decimal(12,2) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderDate, OrderID)
) ON ps_OrderDate (OrderDate);

The partitioning column must be part of the key of every unique index on the table.

Rows per partition

SELECT p.partition_number, prv.value AS lower_boundary, p.rows
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.partition_range_values prv
       ON prv.function_id = ps.function_id AND prv.boundary_id = p.partition_number - 1
WHERE p.object_id = OBJECT_ID(N'dbo.Orders') AND p.index_id IN (0, 1)
ORDER BY p.partition_number;

Monthly sliding window

Always keep an empty partition at the end, and add next month’s boundary before any data for it arrives. Splitting a partition that already holds rows is slow and fully logged.

-- Add next month
ALTER PARTITION SCHEME ps_OrderDate NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2026-04-01');

-- Remove the oldest month by switching it to an empty archive table
-- with the same columns, indexes and filegroup
ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.Orders_Archive;
ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('2026-01-01');

-- Or simply empty one partition (SQL Server 2016 onwards)
TRUNCATE TABLE dbo.Orders WITH (PARTITIONS (1));

Maintenance by partition

-- Use a partition_number from the rows-per-partition query above
ALTER INDEX PK_Orders ON dbo.Orders REBUILD PARTITION = 2 WITH (ONLINE = ON);

With Ola Hallengren’s IndexOptimize, set @PartitionLevel = ‘Y’ so only fragmented partitions are rebuilt.

Watch out for

• Every index should be aligned (created on the same partition scheme). A non-aligned index blocks SWITCH.
• Queries that do not filter on the partitioning column read every partition and can be slower than before.
• Thousands of partitions add compile and maintenance overhead. Monthly partitions are a sensible default.

From my SQL Server DBA handbook.