Under what conditions can a table be partitioned in place?

Question

Context

I have been experimenting with table partitioning and found many surprises. I am currently reading through as much documentation as I can, but there is a shocking amount of it. I think that I'm up to the second of six white papers. I have not seen this question mentioned anywhere in any documentation.

The Problem

Using DROP_EXISTING = ON, it appears that some tables can be partitioned in place. For example, I can get it to work with a unique clustered index. However, I cannot do the same with a primary key. This guide agrees with my conclusion and says that partitioning a table with a primary key in place requires dropping the primary key first. It's as if there is a syntax restriction rather than a functionality one.

My question is this: When can a table be partitioned without dropping anything prior to the partitioning? In other words, when can partitioning be done in place?

I am not specifically asking about what can be done with DROP_EXISTING in particular. That just happened to be one method that I've found.

Assume SQL Server 2022. I do not care if the operation is online or not. I have a different question for something much like that.

Demo Code

Largely stolen from Paul White

CREATE PARTITION FUNCTION PF (integer) 
AS RANGE RIGHT
FOR VALUES 
(
    10000, 20000, 30000, 40000, 50000
);
GO
CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.T1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
CONSTRAINT PK_T1
PRIMARY KEY CLUSTERED (c1, c2, c3)

);
CREATE TABLE dbo.T2
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
INDEX UIX_T2 UNIQUE CLUSTERED (c1, c2, c3)

);
GO
CREATE TABLE dbo.T3
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
INDEX UIX_T3 UNIQUE NONCLUSTERED (c1, c2, c3)

);
GO
/* These work */
CREATE CLUSTERED INDEX UIX_T2 ON dbo.T2 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO
CREATE NONCLUSTERED INDEX UIX_T3 ON dbo.T3 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO
/* But I could never get the primary key to! */

In the above, you can partition unique indexes in place. However, nothing that I've thought to try works for primary keys. I'm pretty sure that REBUILD can do it either.

score 4 · Accepted Answer · edited Mar 30 '25 at 15:53

In the above, you can partition unique indexes in place. However, nothing that I've thought to try works for primary keys.

You can use CREATE INDEX...WITH DROP_EXISTING=ON syntax to partition an existing primary key constraint or unique constraint index similarly to a non-constraint index. The caveats are the specified index key definition must match the existing constraint key, the partitioning column must be part of the unique index key (a requirement for all unique partitioned indexes), and the clustered/nonclustered specification must be the same as the existing index.

This code will partition the primary key constraint index of your sample DDL.

CREATE UNIQUE CLUSTERED INDEX PK_T1 ON dbo.T1 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1);

DROP EXISTING=ON can be used for a primary key constraint index as long as the index key definition is unchanged. The PK will remain afterwards, thus avoiding the overhead of dropping and recreating the constraint. I didn't mention ALTER TABLE...MOVE since that will drop the constraint and supporting index entirely.

Under what conditions can a table be partitioned in place?

Context

The Problem

Demo Code

1 Answers1