Table Partitioning in SQL Server

Partitioning divides a table's rows into units based on the values in one column, while the table still looks and behaves like a single table to every query. SQL Server's implementation is built from two objects -- a partition function and a partition scheme -- and the table is created on the scheme. A table or index can have up to 15,000 partitions.

The first thing to get straight: partitioning is primarily a manageability feature, not a query-speed feature. Switching a month of data out in a metadata operation, truncating one partition instead of deleting millions of rows, rebuilding one partition of an index -- that's what it's for. Queries can get faster through partition elimination, but plenty of partitioned tables see zero query benefit, and some get slower. If your goal is "make this query fast," you usually want an index, not partitions.

Availability note: partitioning was Enterprise-only for years. Since SQL Server 2016 SP1 it's available in Standard, Web, and Express editions too, and it's available in Azure SQL Database.

The Three-Step Setup

1. Partition function

The function maps values to partition numbers by defining boundary values:

CREATE PARTITION FUNCTION pf_orders_monthly (date)
AS RANGE RIGHT FOR VALUES
    ('2026-01-01', '2026-02-01', '2026-03-01', '2026-04-01');

N boundary values always create N + 1 partitions. This function makes five: everything before Jan 2026, then one per month, then everything from April 2026 on.

2. Partition scheme

The scheme maps each partition to a filegroup:

CREATE PARTITION SCHEME ps_orders_monthly
AS PARTITION pf_orders_monthly
ALL TO ([PRIMARY]);

ALL TO ([PRIMARY]) puts every partition on the primary filegroup, which is the normal starting point today. Spreading partitions across filegroups matters mainly for piecemeal restore strategies and tiered storage, not performance on modern SANs and SSDs.

3. Create the table on the scheme

CREATE TABLE orders (
    order_id   bigint IDENTITY NOT NULL,
    order_date date   NOT NULL,
    customer_id int   NOT NULL,
    amount     decimal(12,2) NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (order_date, order_id)
) ON ps_orders_monthly (order_date);

The partitioning column must be part of any unique clustered index key -- which is why order_date leads the primary key here. That constraint routinely forces schema compromises, and it's one of the honest costs of partitioning.

RANGE LEFT vs RANGE RIGHT

This trips up nearly everyone. The question LEFT/RIGHT answers is: which side of the boundary does the boundary value itself belong to?

• RANGE LEFT (the default): the boundary value goes in the partition to its left. Boundary '2026-02-01' means partition 1 holds values <= '2026-02-01'.
• RANGE RIGHT: the boundary value goes in the partition to its right. Boundary '2026-02-01' means partition 2 starts at '2026-02-01'.

For dates, use RANGE RIGHT with first-of-period boundaries. With RANGE LEFT and a boundary of '2026-01-31', a datetime value of 2026-01-31 08:00 lands in the next partition, because it's greater than the boundary -- a classic silent misplacement. RANGE RIGHT on '2026-02-01' puts every instant of January in January's partition, no edge cases.

Verify where rows actually land with the $PARTITION function:

SELECT $PARTITION.pf_orders_monthly(order_date) AS partition_number,
       COUNT(*) AS rows_in_partition,
       MIN(order_date) AS min_date,
       MAX(order_date) AS max_date
FROM orders
GROUP BY $PARTITION.pf_orders_monthly(order_date)
ORDER BY partition_number;

sys.partitions and sys.dm_db_partition_stats give the same information from the metadata side.

Partition Elimination

The optimizer skips partitions when the query has a sargable predicate on the partitioning column:

-- Touches one partition
SELECT SUM(amount) FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';
 
-- Touches EVERY partition: the function on the column kills elimination
SELECT SUM(amount) FROM orders
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 3;

The same sargability rules that apply to indexes apply here: wrap the partitioning column in a function and elimination is gone. Check the actual execution plan -- the seek/scan operator's properties show Actual Partition Count, which tells you exactly how many partitions were touched. (For reading plans in general, see our execution plans guide.)

Aligned Indexes and SWITCH

An index is aligned when it's partitioned on the same scheme (or an identical function) as its base table. Indexes created on a partitioned table without an ON clause are aligned by default.

Alignment is the price of admission for SWITCH -- the metadata operation that moves an entire partition between tables instantly, regardless of row count:

-- Archive January: switch partition 2 out to a staging table
ALTER TABLE orders
SWITCH PARTITION 2 TO orders_archive_stage;

SWITCH requirements are strict: the source and target must have identical structure, identical indexes (all aligned), the same filegroup for the affected partition, and the target must be empty. A non-aligned index anywhere on the table blocks switching entirely -- you'd have to drop or disable it first. If you plan to use sliding windows, keep every index aligned from day one.

The Sliding Window Pattern

The canonical use case: keep the last N months, drop the oldest, add a new one each month.

-- 1. Switch the oldest month out to a staging table (instant)
ALTER TABLE orders SWITCH PARTITION 2 TO orders_purge_stage;
 
-- 2. Truncate the staging table (instant, minimally logged)
TRUNCATE TABLE orders_purge_stage;
 
-- 3. Merge the now-empty boundary away
ALTER PARTITION FUNCTION pf_orders_monthly() MERGE RANGE ('2026-01-01');
 
-- 4. Add next month's boundary at the far end
ALTER PARTITION SCHEME ps_orders_monthly NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_orders_monthly() SPLIT RANGE ('2026-08-01');

The rule that keeps this fast: only ever SPLIT or MERGE empty partitions. Splitting a partition that contains rows physically moves data with roughly four times the log volume of a normal insert, while holding locks. Always keep an empty partition at the leading edge so the monthly SPLIT is a pure metadata change.

Since SQL Server 2016 there's a shortcut for the delete-old-data half -- truncating partitions directly:

TRUNCATE TABLE orders WITH (PARTITIONS (2));
-- Ranges work too: WITH (PARTITIONS (2 TO 4))

That replaces steps 1-2 when you don't need to keep the data. You still MERGE the empty boundary afterward.

What Partitioning Doesn't Do

Honest limitations, because this feature gets oversold:

• It's not an indexing substitute. A seek on a good index beats scanning one partition of 50 million rows.
• Single-row lookups can get slower. Every aligned index seek has to happen per-partition unless the partitioning column is in the predicate -- a lookup by customer_id alone now does one seek per partition.
• The unique-key constraint bites. Every unique index must include the partitioning column, or be non-aligned (which blocks SWITCH). Enforcing "email must be globally unique" on a date-partitioned table is genuinely awkward.
• No automatic partition creation. Unlike PostgreSQL's declarative partitioning with some tooling, you own the monthly SPLIT job. If it stops running, all new data piles into the last partition -- a slow-motion failure that surfaces months later.

Common Mistakes

Checking partition layouts means a lot of probing against sys.partitions, $PARTITION, and sys.dm_db_partition_stats; Mako's AI autocomplete is useful for generating those metadata queries as you explore.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.