Sql server

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

For the person who owns a large SQL Server table and has to decide whether partitioning it is worth the work; partition functions and schemes are explained where they appear.

by Malik Umair Sajjad · 01-Jul-2023 · Updated 09-Sep-2026

On this page

The nightly load, the index rebuild and the retention job each read and write the whole table, and the maintenance window no longer fits all three. SQL Server table partitioning maps the rows of one table onto separate partitions by the value of a single column, so each of those jobs can work on one partition at a time. Queries and applications still address one table.

Three statements build a partitioned table: one for the boundaries, one for the filegroups the partitions live on, one for 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>);

Three boundary values give four partitions. RANGE RIGHT puts each boundary value in the partition above it, RANGE LEFT puts it in the partition below it, and LEFT is what you get when the statement names neither.

What SQL Server table partitioning does

The split is horizontal: groups of rows are mapped into individual partitions, and all partitions of a single table or index reside in the same database, as the SQL Server 2022 documentation on partitioned tables and indexes describes it. Three objects carry the design:

A partition function defines how many partitions there are and where the boundaries between them fall.
A partition scheme maps those partitions to one filegroup or to several.
The partitioning column is the column whose value the partition function reads, and you name it in the CREATE TABLE or CREATE INDEX statement.

What that buys you is work scoped to a subset of the rows:

compress the data in one partition
rebuild one partition of an index
truncate the data in a single partition
switch a partition out of the table and into an archive table

Lock escalation can move from the table to the partition as well, by setting the LOCK_ESCALATION option of ALTER TABLE to AUTO, which can reduce lock contention on the table.

When partitioning helps

Query performance follows one mechanism, partition elimination: when the query predicate references the partitioning column, SQL Server can skip partitions while reading the table or the index. A predicate on any other column reads them all. So the design question is whether the column you would partition on is the column your important queries filter on.

Where that lines up, the wins are the maintenance ones:

time-based fact tables, where monthly or daily partitions turn both the load and the retention policy into per-partition operations
audit and event tables, where the recent data stays uncompressed and the old partitions are compressed or switched out
very large indexes, where rebuild and reorganize work can be scoped to one partition instead of the whole index

Where it does not line up, partitioning costs more than it returns. These query shapes read every partition whatever the boundaries are:

a query that uses TOP, MAX, or MIN on a column other than the partitioning column
a single-row seek whose predicate omits the partitioning column
a small range scan whose predicate omits the partitioning column

The seek and the scan perform as many seeks or scans as there are partitions, so they run longer than the same operation against an unpartitioned table. Microsoft states the conclusion plainly: partitioning rarely improves performance in OLTP systems where such queries are common. Microsoft also advises against designs with many hundreds or thousands of partitions unless they are strictly necessary.

If the real problem is spreading writes over several databases or servers rather than over partitions of one table, the topic you want is SQL Server sharding.

Range, list, and hash partitioning in SQL Server

Searches pair these three words together, so it is worth being exact about which one SQL Server implements. CREATE PARTITION FUNCTION accepts a range function and nothing else, in one of two forms:

CREATE PARTITION FUNCTION pf_Left (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

CREATE PARTITION FUNCTION pf_Right (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

The range type decides which side of each boundary the boundary value itself lands on. With pf_Left, a table partitioned on col1 is split like this:

Partition	Values
1	`col1 <= 1`
2	`col1 > 1` and `col1 <= 100`
3	`col1 > 100` and `col1 <= 1000`
4	`col1 > 1000`

With pf_Right, the same three boundary values split it like this:

Partition	Values
1	`col1 < 1`
2	`col1 >= 1` and `col1 < 100`
3	`col1 >= 100` and `col1 < 1000`
4	`col1 >= 1000`

For a date, datetime, datetime2, or datetimeoffset partitioning column, use RANGE RIGHT: rows stamped midnight then sit in the same partition as the later values of that day, and with monthly partitions the first day of the month sits with the rest of that month, which is what makes partition elimination precise for a query over one whole day.

There is no list partition function and no hash partition function. Both effects come from the partitioning column instead. Only one column can be the partitioning column, and a computed column that participates in a partition function has to be created as PERSISTED, so a persisted computed column that maps a category or a hash to a number gives you a numeric key that a range function then splits one value per partition.

Restrictions on the partitioning column and the indexes

The partitioning column can be of any data type that is valid for an index key column, with these exclusions:

timestamp
the large object types ntext, text, image, xml, varchar(max), nvarchar(max), and varbinary(max)
CLR user-defined types and alias types

The remaining limits come from the engine:

15,000 partitions per table or index, which is 14,999 boundary values, because n boundary values produce n + 1 partitions
rows whose partitioning column is NULL go to the leftmost partition, unless NULL is the first boundary value and the function is RANGE RIGHT, in which case the leftmost partition stays empty and those rows go to the second one
creating the partition function needs ALTER ANY DATASPACE, which members of the sysadmin server role and the db_owner and db_ddladmin database roles have by default, or CONTROL or ALTER on the database
partitioned tables and indexes are available in every edition from SQL Server 2016 (13.x) SP1 on, and in earlier releases only in some editions

Uniqueness is where partitioning reaches into your index design. Partitioning a unique clustered index means adding the partitioning column to the clustering key yourself; SQL Server adds it for you only when the clustered index is not unique. A unique nonclustered index has the same requirement: its key must contain the partitioning column.

Multiple filegroups are optional. Microsoft recommends a single filegroup for all partitions unless you back up and restore filegroups independently or you place cold partitions on cheaper storage, because files and filegroups for partitioned tables add administrative work over time.

If the base table or index does not exist yet, start with SQL Server: How to Create a Table, SQL Server: How to Create an Index, and SQL Server: How to Create a Database.

Create a partitioned table in sqlcmd

Open a session against the server:

sqlcmd -S <server_name> -U <username> -P <password>

The example below builds a monthly partitioned orders table on SQL Server 2022. It keeps every partition on PRIMARY, which leaves the partitioning logic as the only thing to follow:

CREATE DATABASE PartitionDemo;
GO

USE PartitionDemo;
GO

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

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

CREATE TABLE dbo.Orders (
    OrderID    bigint        NOT NULL,
    OrderDate  date          NOT NULL,
    CustomerID int           NOT NULL,
    Amount     decimal(12,2) NOT NULL
)
ON ps_OrdersByMonth(OrderDate);
GO

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

OrderDate is the partitioning column here. Insert one row on either side of each boundary:

INSERT INTO dbo.Orders (OrderID, OrderDate, CustomerID, Amount)
VALUES
    (1, '2025-01-15', 101, 125.00),
    (2, '2025-02-02', 102, 210.00),
    (3, '2025-03-10', 103, 340.00),
    (4, '2025-04-21', 104, 180.00);
GO

Verify partitions and aligned indexes

The $PARTITION function returns the partition number a value maps to, so grouping by it counts the rows that landed in each partition:

SELECT $PARTITION.pf_OrdersByMonth(OrderDate) AS partition_number,
       COUNT(*) AS row_count
FROM dbo.Orders
GROUP BY $PARTITION.pf_OrdersByMonth(OrderDate)
ORDER BY partition_number;

partition_number	row_count
1	1
2	1
3	1
4	1

The January row sits below the first boundary, and each later row sits in the month its boundary opens, because the function is RANGE RIGHT.

An index built on the same partition scheme as its table is an aligned index, and alignment is what lets SQL Server switch partitions in and out of the table quickly while the partition structure of the table and of its indexes stays intact. The clustered index above is aligned because it names the same scheme. A nonclustered index created on a partitioned table without a partition scheme or filegroup of its own is placed on the table's partition scheme with the table's partitioning column, so it is aligned unless you deliberately partition it differently. Reading the alignment back out of the catalog takes one join:

SELECT i.name AS index_name,
       p.partition_number,
       p.data_compression_desc
FROM sys.partitions p
JOIN sys.indexes i
  ON i.object_id = p.object_id
 AND i.index_id = p.index_id
WHERE p.object_id = OBJECT_ID('dbo.Orders')
ORDER BY i.name, p.partition_number;

index_name	partition_number	data_compression_desc
CX_Orders_OrderDate_OrderID	1	NONE
CX_Orders_OrderDate_OrderID	2	NONE
CX_Orders_OrderDate_OrderID	3	NONE
CX_Orders_OrderDate_OrderID	4	NONE

Four rows for one index means the index follows the four partitions of the table. The compression column is the one that changes once you start treating cold partitions differently from hot ones, which is the subject of SQL Server data compression.

Ongoing partition maintenance

Most of the work arrives after the first deployment:

Split a new boundary before the next month arrives.
Switch an old partition out into an archive table.
Merge a partition you no longer need into its neighbor.
Compress cold partitions while the hot ones stay uncompressed.

A split adds a partition through ALTER PARTITION FUNCTION, and the new partition needs a filegroup marked NEXT USED in the scheme to land in. When every partition was created in the same filegroup, that filegroup starts out as NEXT USED automatically, but after the first split there is no NEXT USED filegroup any more, and ALTER PARTITION SCHEME has to assign one before the next split:

ALTER PARTITION SCHEME ps_OrdersByMonth
NEXT USED [PRIMARY];
GO

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

Split ahead of the data, not into it. Microsoft's guidance is to keep an empty partition at each end of the range so that splits and merges move no rows, because splitting or merging a populated partition can generate as much as four times more transaction log and can lock severely. One more thing to check before you run either statement: more than one table or index can use the same partition function, and ALTER PARTITION FUNCTION repartitions all of them in a single transaction, offline.

Design partitioned tables in DbSchema

A partition boundary is one decision inside a design that also has a clustered key, an archive table, a compression policy and a retention job in it, and DbSchema is where you keep that design readable.

Connect through the SQL Server JDBC driver and reverse-engineer the schema into a diagram. DbSchema reads the database and writes what it finds into the .dbs model file, without changing anything in the database.
Fill in the Description field of the partitioning column, so the reason for the choice travels with the schema. Editing the diagram and saving the model change the .dbs file only.
Write and run the partition function, the scheme and the SPLIT statements in the SQL Editor. Those statements change the live database.
Run Schema → Compare Model with Database to see what differs, then Schema → Synchronize Model with Database to generate the migration statements and review them before you execute them, both described on the synchronize with the database page.
Publish the result, with your column descriptions in it, through Diagram → Export HTML5 or PDF Documentation and the schema documentation page.

Partitioning pays off when the partitioning column is the column your queries filter on and your retention job works by. Pick the range type from the data type of that column, keep an empty partition at each end, and let the indexes align themselves. To keep the surrounding design in one place, download DbSchema at https://dbschema.com/download.html, reverse-engineer your SQL Server database, and describe the partitioning column in the diagram; saving that model, comparing it against the live database, and exporting the HTML5 or PDF documentation are Pro edition features.