Table partition existing table where partition key is not part of the primary key

Question

Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
) 
ON [PRIMARY];

This table contains 50 million rows from the last 10 years. I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)
AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS 
AS PARTITION OrderPF ALL TO ([PRIMARY]) 

My question is how to proceed? I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?

Answer 1

If OrderId is monotonically increasing, you can partition on that. Then you can truncate old partitions having no data you need to retain. Something like:

create partition function pf_OrderId(int) 
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go


CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId)
) 
ON ps_OrderId(OrderId)

go

--then you can examine the max OrderDate in each partition when trimming old data
select p.partition_number,
       (select max(OrderDate) MaxOrderDate 
        from [Order] 
        where $PARTITION.pf_OrderId(OrderId) = p.partition_number) MaxOrderDate 
from sys.partitions p
where p.object_id = object_id('Order')
and p.index_id = 1

And of course you can adjust the granularity of your partitions to roughly align to your data retention requirements. And if you have a hard requirement to purge old data then you would truncate N partitions and run a DELETE on at most one partition. And you can always split the partition function to insert a partition boundary at important times, like overnight at the beginning of a year or quarter.

To move an existing table to a partition scheme, you drop all the indexes and the clustered primary key constraint, and recreate them on the new partition scheme. Once you create the clustered index on the partition scheme, subsequently-created indexes will go there by default. If you don't drop the non-clustered indexes first, they will be rebuilt when you drop the clustered PK, and rebuilt again when you recreate it, and they still won't be partitioned. EG

CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId),
    index ix_Order_Orderdate (OrderDate)
) 

go

create partition function pf_OrderId(int) 
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

drop index ix_Order_Orderdate on [Order]

alter table [Order] 
drop constraint [PK_OrderId] 

alter table [Order] 
add constraint [PK_OrderId] primary key clustered (OrderId)
on ps_OrderId(OrderId)

create index ix_Order_Orderdate on [Order](OrderDate)

Then verify that both the clustered and non-clustered indexes are partitioned:

select i.name index_name, p.partition_number 
from sys.partitions p
join sys.indexes i 
 on p.object_id = i.object_id
 and p.index_id = i.index_id 
where p.object_id = object_id('Order')

Answer 2

ALL unique constraints (that would include a primary key) involved in a partitioning scenario MUST have the partition column defined as part of the base index definition (subset of the index key) - (not merely an included column). In your example, you would not be able to partition the nonclustered primary key because orderdate is not part of the base index definition.

I don't think you will achieve your goal of

Add partitioning to table to make deletion of old orders non-blocking/quicker

by using partitioning due to the simple fact that the primary key cannot participate in the same partition scheme/function as the clustered index which you want to partition. Even if you partitioned the primary key on its own partition scheme/function by ID, these two indexes will not be aligned such that you could use partition switching to efficiently delete data with a minimum of logging.

In my opinion, you could create a nonclustered primary key and include the orderdate column which might help the engine find the rows you want to delete faster, but you'd still probably need to do batch deletes in chunks to limit the amount of locking involved.

I just don't think partitioning is the answer for your problem.