Sql-server – Table partition existing table where partition key is not part of the primary key

partitioningsql serversql-server-2017

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?

Best Answer

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')
Related Question