Day 25: Partitioning

A. Lesson overview

Activity breakdown

Business scenario. Order history grows by about 250,000 rows a year. Finance needs the last three full years online; older years go to an archive. Deleting a year row by row takes hours and fills the log. Partitioning by order year turns “archive 2022” into a metadata operation that takes moments.

B. Theory explained clearly

Part 1 — Why partition?

Table partitioning stores one table in several partitions by ranges of one column (the partitioning column), while queries still see one table.

What it is not: a general speed-up. A query that doesn’t filter on the partitioning column must visit every partition, which can be slower than an unpartitioned table with a good index. For many workloads a good index (Day 23) is the better performance tool. Partitioning is available in all editions of SQL Server 2016 SP1 and later, including Standard Developer.

Part 2 — The partition function

A partition function maps values of the partitioning column to partition numbers using boundary values:

CREATE PARTITION FUNCTION pfOrderYear (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01');

n boundaries create n + 1 partitions. RANGE RIGHT means each boundary belongs to the partition on its right — the boundary is the first value of a partition:

With RANGE LEFT, each boundary is the last value of a partition — awkward for dates ('2023-12-31' misses times later that day for datetime2 columns). Use RANGE RIGHT with the first instant of each period for date ranges. Keep an empty partition at each end so you can split without moving data.

Part 3 — Scheme, table and indexes

The partition scheme maps partitions to filegroups. Production systems may spread partitions across filegroups (e.g. older years on cheaper storage); in the training database everything goes to PRIMARY:

CREATE PARTITION SCHEME psOrderYear AS PARTITION pfOrderYear ALL TO ([PRIMARY]);

A table or index is created on the scheme, naming the partitioning column:

CREATE TABLE Perf.SalesOrderPart ( … ,
    CONSTRAINT PK_PerfSalesOrderPart PRIMARY KEY CLUSTERED (OrderDate, OrderID)
) ON psOrderYear (OrderDate);

• A unique index (including the primary key) on a partitioned table must include the partitioning column — SQL Server can only check uniqueness within a partition. Here the key is (OrderDate, OrderID): OrderID is still unique in practice, but the database only guarantees the pair.
• An index partitioned on the same scheme and column is aligned. SWITCH requires every index on the table to be aligned.

Part 4 — Partition elimination

When a query filters on the partitioning column with a sargable predicate, SQL Server reads only the partitions that can contain matching rows. In the actual plan, the operator’s properties show Actual Partition Count and Actual Partitions Accessed. Elimination is lost when the column is wrapped in a function (YEAR(OrderDate) = 2025 — Day 24’s sargability again) or when the query doesn’t filter on that column at all.

Part 5 — The sliding window

SWITCH rules: source and target have the same columns, types, nullability, indexes and filegroup; the target (or target partition) is empty.

Each year: switch out the oldest year, merge its now-empty boundary, split a new empty partition at the end, and switch in new data when it arrives.

C. Dataset and starting state

Starting state. Perf.SalesOrder with 1,000,000 rows. If the partition objects exist from an earlier attempt, remove them first (safe to run at any time; destructive for these objects only):

USE RetailLab;
GO
DROP TABLE IF EXISTS Perf.SalesOrderPart, Perf.SalesOrderArchive, Perf.SalesOrderStage2026;
IF EXISTS (SELECT 1 FROM sys.partition_schemes   WHERE name = 'psOrderYear') DROP PARTITION SCHEME psOrderYear;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'pfOrderYear') DROP PARTITION FUNCTION pfOrderYear;
GO

Validation. Expected: 1000000, 0, 0.

SELECT  (SELECT COUNT(*) FROM Perf.SalesOrder)                                    AS Orders,
        (SELECT COUNT(*) FROM sys.partition_functions WHERE name = 'pfOrderYear')  AS Functions,
        (SELECT COUNT(*) FROM sys.tables WHERE name = 'SalesOrderPart' AND schema_id = SCHEMA_ID('Perf')) AS PartTables;

D. Worked examples

Worked example 1 — Function, scheme, table, load

CREATE PARTITION FUNCTION pfOrderYear (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01');

CREATE PARTITION SCHEME psOrderYear AS PARTITION pfOrderYear ALL TO ([PRIMARY]);

CREATE TABLE Perf.SalesOrderPart
(
    OrderID      int            NOT NULL,
    CustomerID   int            NOT NULL,
    OrderDate    date           NOT NULL,
    SalesChannel varchar(10)    NOT NULL,
    OrderStatus  varchar(10)    NOT NULL,
    ShippingCost decimal(10,2)  NOT NULL,
    Notes        nvarchar(200)  NULL,
    CONSTRAINT PK_PerfSalesOrderPart PRIMARY KEY CLUSTERED (OrderDate, OrderID)
) ON psOrderYear (OrderDate);

INSERT INTO Perf.SalesOrderPart WITH (TABLOCK)
       (OrderID, CustomerID, OrderDate, SalesChannel, OrderStatus, ShippingCost, Notes)
SELECT  OrderID, CustomerID, OrderDate, SalesChannel, OrderStatus, ShippingCost, Notes
FROM    Perf.SalesOrder;

SELECT $PARTITION.pfOrderYear(OrderDate) AS PartitionNumber,
       MIN(OrderDate) AS FirstDate, MAX(OrderDate) AS LastDate, COUNT(*) AS OrderRows
FROM   Perf.SalesOrderPart
GROUP BY $PARTITION.pfOrderYear(OrderDate)
ORDER BY PartitionNumber;

Expected result: Partition scheme ‘psOrderYear’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup… then 4 rows:

Partition 5 exists but is empty, so GROUP BY doesn’t show it. $PARTITION.pfOrderYear(value) tells you which partition any value belongs to — try SELECT $PARTITION.pfOrderYear('2026-06-30'); → 5.

Worked example 2 — The catalog view

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

Expected result: 5 rows — 1 NULL 249834, 2 2023-01-01 249830, 3 2024-01-01 250510, 4 2025-01-01 249826, 5 2026-01-01 0. For RANGE RIGHT, boundary n − 1 is partition n’s lower bound (inclusive). sys.partitions.rows is metadata — instant, no scan.

Worked example 3 — Partition elimination

Turn on Include Actual Execution Plan.

SET STATISTICS IO ON;

SELECT COUNT(*) FROM Perf.SalesOrderPart WHERE OrderDate >= '2024-12-01' AND OrderDate < '2025-01-01';
SELECT COUNT(*) FROM Perf.SalesOrderPart WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 12;
SELECT OrderID, OrderDate FROM Perf.SalesOrderPart WHERE OrderID = 123456;

SET STATISTICS IO OFF;

Expected result: 21217, 21217, and one row (123456, 2024-02-26). In each plan, open the properties of the clustered index operator:

The date range touched only 2024’s partition. The function hid the column (no elimination), and the OrderID lookup had to search every partition because OrderID isn’t the leading key — a single-row lookup that was 3 reads on Perf.SalesOrder now costs far more. Partitioning has a price for queries that don’t use the partitioning column.

E. Guided practical lab — A sliding window

Archive 2022, remove its boundary, and prepare a partition for 2027.

Step 1 — Create the archive table

1. Do: An empty, non-partitioned table with exactly the same structure, on the same filegroup.
2. Run:

   USE RetailLab;
   GO
   CREATE TABLE Perf.SalesOrderArchive
   (
       OrderID      int            NOT NULL,
       CustomerID   int            NOT NULL,
       OrderDate    date           NOT NULL,
       SalesChannel varchar(10)    NOT NULL,
       OrderStatus  varchar(10)    NOT NULL,
       ShippingCost decimal(10,2)  NOT NULL,
       Notes        nvarchar(200)  NULL,
       CONSTRAINT PK_PerfSalesOrderArchive PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   ) ON [PRIMARY];

3. Observe: Commands completed successfully.
4. Why it matters: SWITCH only re-points metadata, so the structures must match exactly.
5. Confirm: SELECT COUNT(*) FROM Perf.SalesOrderArchive; → 0.

Step 2 — Switch 2022 out

1. Do: Move partition 1 to the archive table.
2. Run:

   SET STATISTICS TIME ON;
   ALTER TABLE Perf.SalesOrderPart SWITCH PARTITION 1 TO Perf.SalesOrderArchive;
   SET STATISTICS TIME OFF;
   
   SELECT (SELECT COUNT(*) FROM Perf.SalesOrderArchive)                 AS ArchivedRows,
          (SELECT MIN(OrderDate) FROM Perf.SalesOrderPart)              AS FirstRemainingDate,
          (SELECT COUNT(*) FROM Perf.SalesOrderPart)                    AS RemainingRows;

3. Observe: elapsed time of a few milliseconds; 249834, 2023-01-01, 750166.
4. Why it matters: A quarter of a million rows moved in milliseconds, with almost no log — compare with a DELETE of 249,834 rows.
5. Confirm: partition 1 of Perf.SalesOrderPart has 0 rows (worked example 2’s query).

Step 3 — Merge the empty boundary

1. Do: Remove the 2023-01-01 boundary.
2. Run:

   ALTER PARTITION FUNCTION pfOrderYear() MERGE RANGE ('2023-01-01');

3. Observe: Run worked example 2’s query again: 4 partitions — 1 NULL 249830, 2 2024-01-01 250510, 3 2025-01-01 249826, 4 2026-01-01 0.
4. Why it matters: The empty partition and the 2023 partition combined; partition numbers shifted down by one. Because one side was empty, nothing significant had to move.
5. Confirm: SELECT $PARTITION.pfOrderYear('2023-06-30'); → 1.

Step 4 — Split for 2027

1. Do: Tell the scheme where the next partition goes, then add a boundary in the empty end partition.
2. Run:

   ALTER PARTITION SCHEME psOrderYear NEXT USED [PRIMARY];
   ALTER PARTITION FUNCTION pfOrderYear() SPLIT RANGE ('2027-01-01');

3. Observe: 5 partitions: 1 NULL 249830, 2 2024-01-01 250510, 3 2025-01-01 249826, 4 2026-01-01 0, 5 2027-01-01 0.
4. Why it matters: Splitting the empty last partition is instant. Splitting a partition full of data would physically move rows — always keep an empty partition at the end and split before data arrives.
5. Confirm: SELECT $PARTITION.pfOrderYear('2026-06-30'); → 4.

Step 5 — The table is still one table

1. Do: Query across partitions.
2. Run:

   SELECT YEAR(OrderDate) AS OrderYear, COUNT(*) AS Orders
   FROM   Perf.SalesOrderPart GROUP BY YEAR(OrderDate) ORDER BY OrderYear;

3. Observe: 2023 249830, 2024 250510, 2025 249826.
4. Why it matters: Applications don’t need to know about partitions.
5. Confirm: 3 rows.

F. Independent practice

Task 1 — Switch new data in

Create Perf.SalesOrderStage2026 with the same structure as Perf.SalesOrderPart on [PRIMARY], plus CHECK (OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'). Insert three orders: IDs 1000001–1000003, customers 2, 3 and 4, dated 2026-01-02, Online, Pending, shipping 4.99. Switch the staging table into the 2026 partition.

Success criteria: after the switch, partition 4 has 3 rows; the staging table has 0; SELECT COUNT(*) FROM Perf.SalesOrderPart WHERE OrderDate >= '2026-01-01' → 3.

Task 2 — What blocks a switch?

Create an aligned nonclustered index on Perf.SalesOrderPart (CustomerID) (on psOrderYear (OrderDate)). Try to switch partition 1 (now 2023) to a new empty archive table Perf.SalesOrderArchive2023 that lacks that index. Read the error, add the matching index to the archive table, and switch again.

Success criteria: the first attempt fails with an error saying the target table lacks an index matching the source; the second succeeds with 249,830 rows archived.

Task 3 — Elimination with a join

Count customer 1’s orders in 2024 from Perf.SalesOrderPart joined to Perf.Customer, filtering on o.OrderDate. Then filter on a date range coming from a joined calendar-like table instead (e.g. JOIN (VALUES (CAST('2024-01-01' AS date), CAST('2025-01-01' AS date))) AS r(FromDate, ToDate) ON o.OrderDate >= r.FromDate AND o.OrderDate < r.ToDate). Compare Actual Partitions Accessed.

Success criteria: 12529 both times. The literal filter accesses only the 2024 partition. The join version may still eliminate (SQL Server can use dynamic elimination at run time) — check the plan rather than assuming; with the literal range, elimination is decided at compile time.

Stretch challenge — Automate the window

Write Perf.usp_SlideOrderWindow @ArchiveYear int that, in one transaction: checks that the oldest partition holds exactly @ArchiveYear, switches it to an empty archive table, merges its upper boundary, and splits a new boundary for the year after the current last boundary. Make it fail safely (Day 20 pattern) if anything doesn’t match.

Success criteria: after running it for 2023 (following tasks 1–2 you may need to reset — see section C), the oldest remaining year is 2024 and a boundary for 2028-01-01 exists. Running it again for 2023 fails with a clear error and changes nothing.

G. Solutions and explanations

CREATE TABLE Perf.SalesOrderStage2026
(
    OrderID      int            NOT NULL,
    CustomerID   int            NOT NULL,
    OrderDate    date           NOT NULL,
    SalesChannel varchar(10)    NOT NULL,
    OrderStatus  varchar(10)    NOT NULL,
    ShippingCost decimal(10,2)  NOT NULL,
    Notes        nvarchar(200)  NULL,
    CONSTRAINT PK_PerfSalesOrderStage2026 PRIMARY KEY CLUSTERED (OrderDate, OrderID),
    CONSTRAINT CK_PerfSalesOrderStage2026_Year CHECK (OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01')
) ON [PRIMARY];

INSERT INTO Perf.SalesOrderStage2026 (OrderID, CustomerID, OrderDate, SalesChannel, OrderStatus, ShippingCost)
VALUES (1000001, 2, '2026-01-02', 'Online', 'Pending', 4.99),
       (1000002, 3, '2026-01-02', 'Online', 'Pending', 4.99),
       (1000003, 4, '2026-01-02', 'Online', 'Pending', 4.99);

ALTER TABLE Perf.SalesOrderStage2026 SWITCH TO Perf.SalesOrderPart PARTITION 4;

SELECT (SELECT COUNT(*) FROM Perf.SalesOrderStage2026)                          AS StageRows,
       (SELECT COUNT(*) FROM Perf.SalesOrderPart WHERE OrderDate >= '2026-01-01') AS Rows2026;

CREATE INDEX IX_PerfSalesOrderPart_CustomerID ON Perf.SalesOrderPart (CustomerID) ON psOrderYear (OrderDate);

CREATE TABLE Perf.SalesOrderArchive2023
(
    OrderID int NOT NULL, CustomerID int NOT NULL, OrderDate date NOT NULL,
    SalesChannel varchar(10) NOT NULL, OrderStatus varchar(10) NOT NULL,
    ShippingCost decimal(10,2) NOT NULL, Notes nvarchar(200) NULL,
    CONSTRAINT PK_PerfSalesOrderArchive2023 PRIMARY KEY CLUSTERED (OrderDate, OrderID)
) ON [PRIMARY];

ALTER TABLE Perf.SalesOrderPart SWITCH PARTITION 1 TO Perf.SalesOrderArchive2023;   -- fails

CREATE INDEX IX_PerfSalesOrderArchive2023_CustomerID ON Perf.SalesOrderArchive2023 (CustomerID);
ALTER TABLE Perf.SalesOrderPart SWITCH PARTITION 1 TO Perf.SalesOrderArchive2023;   -- succeeds

SELECT COUNT(*) AS Archived2023 FROM Perf.SalesOrderArchive2023;

SELECT COUNT(*)
FROM   Perf.SalesOrderPart AS o
JOIN   Perf.Customer       AS c ON c.CustomerID = o.CustomerID
WHERE  c.CustomerID = 1 AND o.OrderDate >= '2024-01-01' AND o.OrderDate < '2025-01-01';

SELECT COUNT(*)
FROM   Perf.SalesOrderPart AS o
JOIN   Perf.Customer       AS c ON c.CustomerID = o.CustomerID
JOIN  (VALUES (CAST('2024-01-01' AS date), CAST('2025-01-01' AS date))) AS r (FromDate, ToDate)
       ON o.OrderDate >= r.FromDate AND o.OrderDate < r.ToDate
WHERE  c.CustomerID = 1;

CREATE OR ALTER PROCEDURE Perf.usp_SlideOrderWindow
    @ArchiveYear int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Lower date = DATEFROMPARTS(@ArchiveYear, 1, 1),
            @Upper date = DATEFROMPARTS(@ArchiveYear + 1, 1, 1),
            @LastBoundary date, @NewBoundary date, @Err nvarchar(2048);

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. The oldest partition must hold exactly @ArchiveYear
        IF $PARTITION.pfOrderYear(@Lower) <> 1
           OR NOT EXISTS (SELECT 1 FROM sys.partition_range_values AS v
                          JOIN sys.partition_functions AS f ON f.function_id = v.function_id
                          WHERE f.name = 'pfOrderYear' AND v.boundary_id = 1 AND CAST(v.value AS date) = @Upper)
           OR EXISTS (SELECT 1 FROM Perf.SalesOrderPart WHERE OrderDate < @Lower)
        BEGIN
            SET @Err = FORMATMESSAGE(N'The oldest partition does not hold exactly the year %d.', @ArchiveYear);
            THROW 50500, @Err, 1;
        END;

        -- 2. Empty archive table with matching structure
        DROP TABLE IF EXISTS Perf.SalesOrderArchive;
        CREATE TABLE Perf.SalesOrderArchive
        (
            OrderID int NOT NULL, CustomerID int NOT NULL, OrderDate date NOT NULL,
            SalesChannel varchar(10) NOT NULL, OrderStatus varchar(10) NOT NULL,
            ShippingCost decimal(10,2) NOT NULL, Notes nvarchar(200) NULL,
            CONSTRAINT PK_PerfSalesOrderArchive PRIMARY KEY CLUSTERED (OrderDate, OrderID)
        ) ON [PRIMARY];

        -- 3. Switch out, merge, split
        ALTER TABLE Perf.SalesOrderPart SWITCH PARTITION 1 TO Perf.SalesOrderArchive;
        ALTER PARTITION FUNCTION pfOrderYear() MERGE RANGE (@Upper);

        SELECT @LastBoundary = MAX(CAST(v.value AS date))
        FROM   sys.partition_range_values AS v
        JOIN   sys.partition_functions    AS f ON f.function_id = v.function_id
        WHERE  f.name = 'pfOrderYear';
        SET @NewBoundary = DATEADD(YEAR, 1, @LastBoundary);

        ALTER PARTITION SCHEME psOrderYear NEXT USED [PRIMARY];
        ALTER PARTITION FUNCTION pfOrderYear() SPLIT RANGE (@NewBoundary);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

-- Reset to the lab's end state if needed (section C, worked example 1, lab steps 1–4), then:
EXEC Perf.usp_SlideOrderWindow @ArchiveYear = 2023;
EXEC Perf.usp_SlideOrderWindow @ArchiveYear = 2023;   -- fails: 50500, nothing changes

H. Knowledge check

1. Understanding. With RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01'), which partition holds 2024-01-01, and how many partitions are there?

2. Understanding. Why must the primary key of Perf.SalesOrderPart include OrderDate?

3. Query interpretation. A plan on a partitioned table shows Actual Partitions Accessed: 1..5 for a query filtering on YEAR(OrderDate) = 2025. Why, and what’s the fix?

4. Debugging. ALTER TABLE stage SWITCH TO t PARTITION 4 fails, complaining that a CHECK constraint on the source doesn’t allow values outside the target partition’s range. The constraint exists. What do you check?

5. Understanding. Why should you split a new boundary before data for that period arrives?

I. Troubleshooting and cleanup

Cleanup — end of the performance block. Remove today’s objects (destructive for these objects only):

DROP PROCEDURE IF EXISTS Perf.usp_SlideOrderWindow;
DROP TABLE IF EXISTS Perf.SalesOrderPart, Perf.SalesOrderArchive, Perf.SalesOrderArchive2023, Perf.SalesOrderStage2026;
IF EXISTS (SELECT 1 FROM sys.partition_schemes   WHERE name = 'psOrderYear') DROP PARTITION SCHEME psOrderYear;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'pfOrderYear') DROP PARTITION FUNCTION pfOrderYear;

Freeing disk space (optional). Day 30’s monitoring exercise works better with the Perf data present but doesn’t require it. If space is tight, drop all Perf tables by re-running the first part of script 05 — or simply leave them; script 05 can rebuild everything at any time. Lab.PerfBaseline can be dropped when you no longer need your measurements.

J. Completion checklist, recap and references

Completion checklist

• Rows per partition: 249834 / 249830 / 250510 / 249826 / 0.
• Elimination: the date range accessed 1 partition; YEAR() and OrderID accessed all 5.
• Switching out 2022 took milliseconds; the archive has 249,834 rows.
• After merge and split: boundaries 2024-01-01, 2025-01-01, 2026-01-01, 2027-01-01.
• Task 1 switched 3 rows into the 2026 partition.

Recap

• Partitioning is mainly a data-management tool; it speeds up queries that filter on the partitioning column and can slow those that don’t.
• RANGE RIGHT with period start dates; keep empty partitions at both ends.
• Unique keys must include the partitioning column; aligned indexes are needed for SWITCH.
• Elimination needs sargable predicates on the partitioning column — check Actual Partitions Accessed.
• Sliding window: switch out, merge an empty boundary, NEXT USED + split an empty partition, switch in with a trusted CHECK.

Next: Day 26 — Advanced T-SQL features: sequences, synonyms, schemas and permissions, computed and persisted columns, and Dynamic Data Masking.

References (Microsoft Learn)

• Partitioned tables and indexes
• CREATE PARTITION FUNCTION (Transact-SQL)
• CREATE PARTITION SCHEME (Transact-SQL)
• ALTER PARTITION FUNCTION (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• $PARTITION (Transact-SQL)
• Editions and supported features of SQL Server 2025