Main objective: Add partitioning to the table to make the removal of old non-blocking / faster orders (and understand partitioning)
I have an existing table order, like this:
CREATE TABLE Command ( OrderId INT, Date of order Date / time, Quantity INT, CONSTRAINT [PK_OrderId] CLUSTERED PRIMARY KEY ( [OrderId] ASC ) SURE [PRIMARY];
This table contains 50 million lines of the last 10 years.
I only need data from the last 5 years.
I have a partition function like this:
CREATE PARTITION FUNCTION OrderPF (date / time) A RANGE OF RIGHTS TO VALUES (& # 39; 2014-01-01 & # 39;)
I have a partition scheme like this:
CREATE A PARTITION SYSTEM AS A PARTITIONING OrderPF ALL TO ([PRIMARY])
My question is how?
I still want a primary key on the table.
Is the [OrderDate] column should be part of the clustered index? (Main question)
CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order (OrderDate, OrderId) ON OrderPS (OrderDate);
If so, should I then create an additional non-clustered primary key only on [OrderId]?
ALTER TABLE Order ADD CONTRAINT PK_OrderId PRIMARY KEY NON CLUSTERED (Id) ON [PRIMARY];
Is this the right approach?