3

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?

  • 1
    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. – Scott Hodgin Feb 25 at 12:34
  • Thanks @ScottHodgin. What statements would you suggest to achieve what I'm trying to do? – Dag Feb 25 at 12:45
  • @Dag, if your objective is to use partition-level TRUNCATE to purge old orders, the indexes must be aligned (partitioned similarly). You could change your clustered PK to OrderId, OrderDate (assuming ID is a typo since it's not in your table DDL). – Dan Guzman Feb 25 at 12:57
2

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')
  • Thanks David. I believe this would work very well. However, your sql implies creating a new table. What would the statements be for altering an existing table to achieve the same? – Dag Feb 26 at 14:31
  • See edit for how to move an existing table. – David Browne - Microsoft Feb 26 at 14:48
  • Excellent David. Thanks. One follow up question - if I still wanted to have the partition on the date column, not the ID column. What would the primary key statement be then? Would it have to be on (orderdate, orderid) and an nonclustered index on (orderid)? or an clustered index on (orderdate, orderid), and the primary key an nonclustered index on orderid ? – Dag Feb 27 at 12:06
  • You would need a non-clustered primary key on OrderId, which would be non-partitioned. Then you could use a partitioned clustered index including OrderDate as a key, a partitioned heap, or a partitioned Clustered Columstore index to store the rows. But you wouldn't be able to truncate a single partition without dropping the PK first. – David Browne - Microsoft Feb 27 at 13:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.