SQL Server Table Partitioning: Partition Functions, Schemes, RANGE LEFT/RIGHT, and Best Practices

Table partitioning splits the rows of one SQL Server table into separate parts, called partitions, by the value of one column. For example, you can keep each month of an orders table in its own partition. A nightly load, an index rebuild or a job that removes old orders can then work on one month instead of the whole table.

Three statements create a partitioned table: a function for the boundaries, a scheme for the storage, and the table itself.

CREATE PARTITION FUNCTION function_name (column_type)
AS RANGE RIGHT FOR VALUES (boundary_1, boundary_2, boundary_3);

CREATE PARTITION SCHEME scheme_name
AS PARTITION function_name ALL TO ([PRIMARY]);

CREATE TABLE table_name (column_list)
ON scheme_name (partitioning_column);

• CREATE PARTITION FUNCTION lists the boundary values, where one partition ends and the next one starts. Three boundaries make four partitions.
• column_type is the data type of the partitioning column, the one column whose value picks the partition.
• RANGE RIGHT puts each boundary value in the partition on its right. RANGE LEFT puts the value in the partition on its left.
• CREATE PARTITION SCHEME maps each partition to a filegroup, a named set of database files. ALL TO ([PRIMARY]) puts every partition in the primary filegroup.
• ON scheme_name (partitioning_column) stores the table on the scheme. SQL Server picks each row's partition from the row's value in that column.

Create the sample table, an ordinary orders table with four rows, like a table you already have:

CREATE TABLE dbo.Orders (
    OrderID   int           NOT NULL,
    OrderDate date          NOT NULL,
    Amount    decimal(10,2) NOT NULL
);

INSERT INTO dbo.Orders (OrderID, OrderDate, Amount) VALUES
    (1, '2025-01-15', 120.00),
    (2, '2025-02-01',  80.00),
    (3, '2025-02-20',  45.00),
    (4, '2025-03-10', 300.00);

Order 2 is dated exactly on the first day of a month. RANGE LEFT and RANGE RIGHT put such a date in different partitions. No order is dated April or later yet.

The statements follow the Microsoft Learn reference for SQL Server 2022. Before SQL Server 2016 SP1, partitioning was available only in some editions.

CREATE PARTITION FUNCTION

A partition function holds the boundary values and decides which partition each value goes to. For example, this function starts a new partition on the first day of each month.

Create the function first. The scheme and the table both name it.

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

The three boundaries split every possible date into four partitions:

In a RANGE RIGHT function, each boundary value starts a partition. Partition 1 takes every date before February, and partition 4 takes every date from April on. The function holds no rows; it only says where a row would go.

RANGE LEFT and RANGE RIGHT

The range type, LEFT or RIGHT, decides whether the boundary value itself goes to the partition on its left or on its right. For example, order 2 is dated on the boundary 2025-02-01. Here is the same function with RANGE LEFT:

CREATE PARTITION FUNCTION pf_OrdersByMonthLeft (date)
AS RANGE LEFT FOR VALUES ('2025-02-01', '2025-03-01', '2025-04-01');

Each boundary value now ends a partition:

Under RANGE LEFT, order 2 would share partition 1 with order 1 from January. Every partition would run from the 2nd of one month to the 1st of the next.

RANGE RIGHT keeps each calendar month in one partition. Microsoft notes that RIGHT ranges are often simpler for date and time columns.

A function that names neither type gets RANGE LEFT, the default of CREATE PARTITION FUNCTION. SQL Server has no list or hash partition function, only range.

To partition by a category or a hash, partition on a computed column that turns the category or the hash into a number. Create that column as PERSISTED. A persisted column stores its values in the table.

CREATE PARTITION SCHEME

A partition scheme says in which filegroup each partition is stored. For example, ALL TO can store every partition in the primary filegroup.

CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth ALL TO ([PRIMARY]);

ALL also marks PRIMARY as the NEXT USED filegroup, the one that receives the next new partition. To spread the partitions over several filegroups, name one filegroup per partition in place of ALL TO ([PRIMARY]).

Microsoft recommends a single filegroup unless you back up and restore filegroups separately, or keep old partitions on cheaper storage.

ON a partition scheme

The ON clause stores a table or an index on a partition scheme. For a table that holds rows but no clustered index, such as dbo.Orders, create its clustered index on the scheme. CREATE INDEX moves the table's rows onto the scheme with the index.

CREATE CLUSTERED INDEX CX_Orders
ON dbo.Orders (OrderDate, OrderID)
ON ps_OrdersByMonth (OrderDate);

OrderDate is now the partitioning column. Order 1 goes to partition 1, orders 2 and 3 to partition 2, and order 4 to partition 3. Partition 4 stays empty until an April order arrives.

For a new table, put the same ON clause at the end of CREATE TABLE, as in the syntax above.

A clustered index on a partition scheme has to contain the partitioning column. If a nonunique clustered index lacks the column, SQL Server adds it. A unique clustered index has to list the column explicitly.

If the table already has a clustered index, recreate that index on the scheme with the DROP_EXISTING option of CREATE INDEX.

$PARTITION

$PARTITION returns the number of the partition that a value goes to. For example, you can list each order with the number of its partition.

SELECT OrderID, OrderDate,
       $PARTITION.pf_OrdersByMonth(OrderDate) AS partition_number
FROM dbo.Orders
ORDER BY OrderID;

The query returns the partition numbers 1, 2, 2 and 3 for orders 1 to 4, as in the diagram above.

$PARTITION needs only the function and a value. SELECT $PARTITION.pf_OrdersByMonth('2025-04-10') returns 4, although no order has that date yet.

sys.partitions

sys.partitions is a built-in catalog view with one row for each partition of each table and index. For example, its rows column shows how many orders each partition of dbo.Orders holds.

SELECT partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.Orders')
  AND index_id = 1
ORDER BY partition_number;

index_id = 1 picks the clustered index, which holds the table's rows. The query returns partitions 1 to 4, with 1, 2, 1 and 0 rows.

Where you need an exact number, count the rows with $PARTITION. sys.partitions calls its rows count approximate.

Each nonclustered index has its own rows in the view. An index on the same partition scheme as its table is an aligned index, with the same partitions.

The data_compression_desc column shows which partitions are compressed, as SQL Server data compression explains.

SPLIT RANGE

SPLIT RANGE adds a boundary to a partition function, and so turns one partition into two. For example, you can add a boundary at 2025-05-01 before any May order arrives.

ALTER PARTITION SCHEME ps_OrdersByMonth NEXT USED [PRIMARY];

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

Partition 4 now ends before 2025-05-01, and a new partition 5 starts there. Both partitions are empty, so no row had to move. The scheme put partition 5 in PRIMARY, the NEXT USED filegroup.

Run the NEXT USED statement before every split. The first split could use the mark that ALL TO set on PRIMARY, but a split clears that mark. Without a new mark, the next split fails.

SWITCH

ALTER TABLE ... SWITCH moves one partition into another table. For example, you can move January's orders into an archive table in one statement, instead of deleting them row by row.

CREATE TABLE dbo.OrdersArchive (
    OrderID   int           NOT NULL,
    OrderDate date          NOT NULL,
    Amount    decimal(10,2) NOT NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX CX_OrdersArchive
ON dbo.OrdersArchive (OrderDate, OrderID);

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

Order 1 now belongs to dbo.OrdersArchive, and partition 1 of dbo.Orders is empty.

The target of a SWITCH has to match the partition. dbo.OrdersArchive has the same columns and clustered index as dbo.Orders, and it is stored in PRIMARY, the filegroup of partition 1. ALTER TABLE also requires the target table to be empty.

If you don't need the old rows at all, TRUNCATE TABLE dbo.Orders WITH (PARTITIONS (1)) deletes them without moving them. TRUNCATE TABLE accepts that clause from SQL Server 2016 on, and only when every index is aligned.

MERGE RANGE

MERGE RANGE removes a boundary from a partition function, and so joins two partitions into one. For example, after the switch you can remove the oldest boundary, 2025-02-01, while partition 1 is empty.

ALTER PARTITION FUNCTION pf_OrdersByMonth() MERGE RANGE ('2025-02-01');

Partitions 1 and 2 are now one partition, numbered 1. It holds every date before 2025-03-01, so it holds orders 2 and 3. The partitions after it are numbered again from 2: March, then April, then May and later.

Together, SPLIT RANGE, SWITCH and MERGE RANGE make a monthly sliding window: each month, add a partition at the end, switch the oldest one out, and remove the oldest boundary.

What goes wrong

Queries that skip the partitioning column

When the WHERE clause filters on the partitioning column, SQL Server can skip partitions. This partition elimination makes a query faster.

The first query below can read March's partition alone. The second query filters on OrderID, so it has to read every partition.

SELECT SUM(Amount) FROM dbo.Orders
WHERE OrderDate >= '2025-03-01' AND OrderDate < '2025-04-01';

SELECT Amount FROM dbo.Orders
WHERE OrderID = 4;

Even with an index on OrderID, a lookup of one OrderID searches the index once in each partition. That lookup takes longer than on the same table unpartitioned, and TOP, MAX and MIN on another column can slow down too. Microsoft concludes that partitioning rarely improves performance in OLTP systems, which run many short queries like this one.

If your loads, archive jobs and index rebuilds work on ranges of the partitioning column, partition the table. To spread a table over several servers, read SQL Server sharding instead.

A primary key without the partitioning column

A primary key on a partitioned table creates a unique index on the table's partition scheme. A unique index on a partition scheme has to contain the partitioning column. So this primary key on OrderID alone fails:

ALTER TABLE dbo.Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderID);

Add OrderDate to the key, as PRIMARY KEY NONCLUSTERED (OrderID, OrderDate). That key makes each pair of OrderID and OrderDate unique, so the same OrderID could appear on two dates.

A nonunique index needs no change. SQL Server adds the partitioning column to it, so the index stays aligned.

Splitting a partition that holds rows

A split inside a partition that holds rows has to move some of those rows. For example, the boundary 2025-02-15 falls between orders 2 and 3, which share a partition. SQL Server has to separate the two orders:

ALTER PARTITION SCHEME ps_OrdersByMonth NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pf_OrdersByMonth() SPLIT RANGE ('2025-02-15');

Keep the first and the last partition empty, and split and merge only there. On a large table, splitting or merging partitions with rows can write up to four times more transaction log, and can hold heavy locks.

Before you split or merge a function, check which tables share it. ALTER PARTITION FUNCTION changes every table and index that uses the function, offline and in one transaction.

RANGE LEFT on a datetime column

A RANGE LEFT function on a datetime column needs the last moment of each month as its boundary. The obvious value for January is wrong:

CREATE PARTITION FUNCTION pf_OrdersByMonthTime (datetime)
AS RANGE LEFT FOR VALUES ('20250131 23:59:59.999');

SQL Server rounds a datetime value to .000, .003 or .007 seconds. The boundary in this function is stored as 2025-02-01 00:00:00.000. An order placed at midnight on February 1 then lands in January's partition.

The last datetime value of January is '20250131 23:59:59.997'. A RANGE RIGHT function avoids the problem: its boundary is '20250201', the first moment of February.

Limits and permissions

SQL Server has these limits, listed on the partitioned tables and indexes page and in CREATE PARTITION FUNCTION:

• One partitioning column only. A computed column can combine several columns, and has to be PERSISTED.
• Not timestamp, ntext, text, image, xml, varchar(max), nvarchar(max), varbinary(max), alias types or CLR types.
• Up to 15,000 partitions per table or index, but Microsoft advises against many hundreds or thousands.
• Rows with NULL in the partitioning column go to the leftmost partition, unless NULL is a boundary value of a RANGE RIGHT function.
• Creating a partition function needs ALTER ANY DATASPACE, CONTROL or ALTER on the database, or CONTROL SERVER or ALTER ANY DATABASE on the server.

Run the statements in DbSchema

The SQL Editor in DbSchema runs T-SQL on a connected SQL Server. Press Editor in the toolbar, choose SQL Editor, and paste the statements from CREATE TABLE dbo.Orders to CREATE CLUSTERED INDEX.

Run the pasted statements with Run Script. Run Script runs them one after the other, and stops at the first one that fails. These statements change the live database.

Then run the $PARTITION query with Run. The result grid shows each order's partition.

Download DbSchema at https://dbschema.com/download.html, connect it to your SQL Server database, and try the partition function and $PARTITION on a copy of your table. Connecting to a database and the SQL Editor are part of the free Community Edition.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download