Narcis Miclaus

5 min read

How partitioning actually works in SQL Server

A walkthrough of partition functions, partition schemes, the sliding-window pattern, and the gotchas nobody warns you about until your overnight job is on fire.

If you’ve ever inherited a fact table with two billion rows and been told “just add a partition,” you know the docs make this sound simpler than it is. Here’s the version I wish someone had handed me on day one.

What partitioning is — and isn’t

Partitioning splits one logical table into many physical chunks based on the value of a column. To anyone running SELECT, the table still looks like one table. To the storage engine, it’s a row of buckets, and queries that filter on the partition column only have to open the buckets they need.

Two things partitioning is not:

  • A free performance win. A bad partition scheme is slower than no partitioning at all, because the optimizer now has to deal with partition elimination on top of everything else.
  • A replacement for an index. Partitioning helps you prune whole chunks of the table. Indexes help you find rows. You almost always want both.

The big win is operational: you can swap a billion rows in or out of a partitioned table in milliseconds with ALTER TABLE ... SWITCH, because all SQL Server has to do is re-point a metadata pointer. No data movement.

The three pieces

Every partitioned table in SQL Server is built from three things:

  1. A partition function — defines the boundaries between buckets.
  2. A partition scheme — maps those buckets onto filegroups.
  3. A table or index built on the scheme.

Here’s the smallest end-to-end example. We’re partitioning an Orders table by month on OrderDate.

-- 1. Partition function: one boundary per month
CREATE PARTITION FUNCTION pf_OrdersByMonth (DATETIME2(0))
AS RANGE RIGHT FOR VALUES (
    '2025-01-01', '2025-02-01', '2025-03-01',
    '2025-04-01', '2025-05-01', '2025-06-01'
);

-- 2. Partition scheme: send every partition to PRIMARY
--    (in production you'd usually spread these across filegroups)
CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth
ALL TO ([PRIMARY]);

-- 3. The table itself, built on the scheme
CREATE TABLE dbo.Orders (
    OrderId     BIGINT       NOT NULL,
    CustomerId  INT          NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderDate, OrderId)
        ON ps_OrdersByMonth (OrderDate)
);

Two things to notice:

  • RANGE RIGHT vs RANGE LEFT. RANGE RIGHT puts the boundary value into the upper partition. '2025-02-01' lives in the February bucket, not January. This is almost always what you want for date partitioning, because you can describe a month as a clean half-open interval [start, next_start). Using RANGE LEFT with dates creates off-by-one bugs that take a frustrating afternoon to debug.
  • The partition column has to be in the clustered key. That’s why the primary key above is (OrderDate, OrderId) instead of just OrderId. You’ll see this come up in every SQL Server partition tutorial and it always feels wrong the first time. Live with it.

The sliding window

The pattern that makes partitioning earn its keep is the sliding window: every month (or day, or hour), you add a new empty partition at the leading edge and switch out the oldest one.

-- Add a new partition for next month.
-- Tell the scheme which filegroup the new partition will live on first.
ALTER PARTITION SCHEME ps_OrdersByMonth
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pf_OrdersByMonth ()
    SPLIT RANGE ('2025-07-01');

-- Switch the oldest month out into a staging table for archive/delete.
-- The staging table must have the same schema, indexes, and constraints,
-- and live on the same filegroup as the partition you're switching out.
CREATE TABLE dbo.Orders_Archive_2024_12 (
    OrderId     BIGINT       NOT NULL,
    CustomerId  INT          NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    CONSTRAINT pk_Orders_Archive_2024_12 PRIMARY KEY CLUSTERED (OrderDate, OrderId),
    CONSTRAINT ck_Orders_Archive_2024_12_Range
        CHECK (OrderDate >= '2024-12-01' AND OrderDate < '2025-01-01')
);

ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.Orders_Archive_2024_12;

-- Now collapse the empty partition that's been left behind.
ALTER PARTITION FUNCTION pf_OrdersByMonth ()
    MERGE RANGE ('2025-01-01');

That SWITCH is the magic moment. It’s a metadata operation — milliseconds — regardless of whether the partition holds 100 rows or 100 million. You then drop, archive, or backup Orders_Archive_2024_12 at your leisure, without holding any locks on the live table.

Tips that actually save you

  • Always keep an empty partition at each end. Splitting a partition that already contains rows logs every row movement. Splitting an empty partition is instant. Make “the next month’s empty partition” something a scheduled job creates well in advance.
  • Use $PARTITION to verify before you act. SELECT $PARTITION.pf_OrdersByMonth(OrderDate), COUNT(*) FROM dbo.Orders GROUP BY $PARTITION.pf_OrdersByMonth(OrderDate); tells you exactly how many rows live in each partition. Run this before any SWITCH so you can never accidentally archive the wrong month.
  • Aligned indexes only. Every non-clustered index on a partitioned table should be partitioned the same way (ON ps_OrdersByMonth (OrderDate)). Non-aligned indexes block partition switching entirely — SQL Server will reject the SWITCH with a confusing error and you’ll lose 20 minutes finding out why.
  • Watch your statistics. Partitioned tables can develop badly skewed statistics on the “current” partition because all the writes go there. Update stats on the live partition often, not the whole table.
  • Test the constraint. The CHECK constraint on the staging table is what tells SQL Server the switch is safe. Get the inequality wrong (<= instead of < for a RANGE RIGHT boundary) and the switch fails. Always paste the boundary date into both sides of your function and your check constraint — copy-paste, don’t retype.

When not to partition

Partitioning isn’t free. It costs you query plan complexity, more moving parts at deploy time, and a whole new class of bugs (non-aligned indexes, statistics skew, mismatched check constraints). It pays for itself when:

  • You have a clear time-based access pattern (the last month is hot, older months are cold).
  • You need fast bulk loads or fast bulk deletes that don’t lock the table.
  • The table is big enough that scanning the whole thing for archival is painful — usually meaning hundreds of millions of rows or more.

If your table is 5 million rows and you’re partitioning it because someone read a blog post, please don’t. A good clustered index and a query rewrite will get you most of the way there, with none of the operational overhead.