Partitioning

37.1 Concepts

Splits a table or index horizontally into partitions. It remains one logical object.

Benefits: manageability (fast loads and archival through partition switching), partition elimination, per-partition maintenance and compression.

Partitioning is not primarily a query-performance feature.

37.2 Implementation

RANGE LEFT vs RANGE RIGHT decides which side the boundary value falls on.

Aligned indexes use the same partition scheme. They're required for SWITCH.

Partition-level operations: TRUNCATE ... WITH (PARTITIONS(...)), REBUILD PARTITION = n, incremental statistics, lock escalation AUTO.

-- 1. Partition function
CREATE PARTITION FUNCTION pf_OrderYear (DATETIME2(0))
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01');

-- 2. Partition scheme
CREATE PARTITION SCHEME ps_OrderYear
AS PARTITION pf_OrderYear ALL TO ([PRIMARY]);   -- or map to filegroups

-- 3. Table on the scheme (partition column must be part of the clustered key)
CREATE TABLE dbo.OrdersPart
(
    OrderID INT NOT NULL, OrderDate DATETIME2(0) NOT NULL, Amount DECIMAL(12,2),
    CONSTRAINT PK_OrdersPart PRIMARY KEY CLUSTERED (OrderDate, OrderID)
) ON ps_OrderYear(OrderDate);

-- Inspect
SELECT $PARTITION.pf_OrderYear('2024-06-15') AS PartitionNo;
SELECT partition_number, rows FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.OrdersPart') AND index_id = 1;

-- Sliding window maintenance
ALTER PARTITION SCHEME ps_OrderYear NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_OrderYear() SPLIT RANGE ('2027-01-01');
ALTER TABLE dbo.OrdersPart SWITCH PARTITION 1 TO dbo.OrdersArchive;   -- metadata-only
ALTER PARTITION FUNCTION pf_OrderYear() MERGE RANGE ('2023-01-01');