partitioning a table on a column that does not belong to the current clustered index

Question

I am partitioning the following table, 300 million rows, data from 2011 until today (until 24-mar-2017 in the test system)

I want to partition my table over a column called OrderDateSID int that has the date in the following format for example: 20170401 for April 1st 2017

This is my table definition, it is quite long and include all the current indexes as well:

IF OBJECT_ID('[Facts].[FactOrder]') IS NOT NULL 
DROP TABLE [Facts].[FactOrder] 
GO
CREATE TABLE [Facts].[FactOrder] ( 
[FactOrderSID]              BIGINT                           NOT NULL,
[OrderNo]                   VARCHAR(20)                          NULL,
[OrderItemSeqNo]            SMALLINT                             NULL,
[OrderNoItemSeqNo]          VARCHAR(30)                          NULL,
[OrderDetailsSID]           INT                                  NULL,
[ChannelSID]                INT                                  NULL,
[OrderDateSID]              INT                                  NULL,
[OrderTypeSID]              INT                                  NULL,
[CoreStatusSID]             INT                                  NULL,
[ProductSubSeasonID]        INT                                  NULL,
[OrderSubSeasonID]          INT                                  NULL,
[MarketSID]                 INT                                  NULL,
[ProductSID]                INT                                  NULL,
[CurrencyCodeSID]           INT                                  NULL,
[LocalOrderDateSID]         INT                                  NULL,
[FullSellingPrice]          NUMERIC(18,4)                        NULL,
[PriceSetPrice]             NUMERIC(18,4)                        NULL,
[ActualSellingPrice]        NUMERIC(18,4)                        NULL,
[ActualTaxableAmount]       NUMERIC(18,4)                        NULL,
[CostofGoodsAmount]         NUMERIC(18,4)                        NULL,
[ShipFullPrice]             NUMERIC(18,4)                        NULL,
[ShipActualPrice]           NUMERIC(18,4)                        NULL,
[ShipActualTaxAmount]       NUMERIC(18,4)                        NULL,
[BCFullSellingPrice]        NUMERIC(18,4)                        NULL,
[BCPriceSetPrice]           NUMERIC(18,4)                        NULL,
[BCActualSellingPrice]      NUMERIC(18,4)                        NULL,
[BCActualTaxableAmount]     NUMERIC(18,4)                        NULL,
[BCCostofGoodsAmount]       NUMERIC(18,4)                        NULL,
[BCShipFullPrice]           NUMERIC(18,4)                        NULL,
[BCShipActualPrice]         NUMERIC(18,4)                        NULL,
[BCShipActualTaxAmount]     NUMERIC(18,4)                        NULL,
[UnitsSold]                 INT                                  NULL,
[LoadID]                    INT                                  NULL,
[ActualProductSID]          INT                                  NULL,
[AccountSID]                INT                              NOT NULL,
[PriceDiscount]             NUMERIC(18,4)                        NULL,
[BCPriceDiscount]           NUMERIC(18,4)                        NULL,
[PromotionalDiscount]       NUMERIC(18,4)                        NULL,
[BCPromotionalDiscount]     NUMERIC(18,4)                        NULL,
[GrossDemand]               NUMERIC(18,4)                        NULL,
[BCGrossDemand]             NUMERIC(18,4)                        NULL,
[UKFSPIncVAT]               NUMERIC(18,4)                        NULL,
[UKFSPExVAT]                NUMERIC(18,4)                        NULL,
[BCTotalDiscount]           NUMERIC(18,4)                        NULL,
[TotalDiscount]             NUMERIC(18,4)                        NULL,
[OrderNoItemSeqNoSID]       INT                              NOT NULL,
[OfferSID]                  INT                                  NULL,
[MarkdownStatusSID]         INT                              NOT NULL,
[AdvertisedPrice]           NUMERIC(18,4)                        NULL,
[BCAdvertisedPrice]         NUMERIC(18,4)                        NULL,
[CataloguePriceSetTypeSID]  INT                              NOT NULL,
[UserSID]                   INT                                  NULL,
CONSTRAINT   [PK_FactOrder]  
PRIMARY KEY CLUSTERED    ([FactOrderSID] asc) WITH FILLFACTOR = 100)

GO

CREATE NONCLUSTERED INDEX [Facts_FactOrder_LoadID] 
   ON [Facts].[FactOrder] ([LoadID] asc)
   INCLUDE ([FactOrderSID])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [Facts_FactOrder_OrderDateSID] 
   ON [Facts].[FactOrder] ([OrderDateSID] asc)
   INCLUDE ([FactOrderSID])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [Facts_FactOrder_OrderNoItemSeqNoSID] 
   ON [Facts].[FactOrder] ([OrderNoItemSeqNoSID] asc)
   INCLUDE ([FactOrderSID], [OrderDateSID])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IDX_Facts_FactOrder_Channel_INC_AccountSID] 
   ON [Facts].[FactOrder] ([AccountSID] asc)
   INCLUDE ([ChannelSID])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_Facts_FactOrder_OrderDetailsSID] 
   ON [Facts].[FactOrder] ([OrderDetailsSID] asc)
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_Facts_FactOrder_UKFSPExVAT__INCLU] 
   ON [Facts].[FactOrder] ([UKFSPExVAT] asc)
   INCLUDE ([ActualProductSID], [MarketSID])
   WITH FILLFACTOR = 100

I have created the partition function

----------------------------------------------------------------------------
--- create a partition function
----------------------------------------------------------------------------

use radhe
go


IF EXISTS (SELECT * FROM sys.partition_functions
    WHERE name = 'PF_Facts_FactOrder_OrderDateSID')
DROP PARTITION FUNCTION PF_Facts_FactOrder_OrderDateSID ;
GO

CREATE PARTITION FUNCTION PF_Facts_FactOrder_OrderDateSID (int)
AS RANGE LEFT FOR VALUES (  20110101,
                            20120101,
                            20130101,
                            20140101,
                            20150101,
                            20160101,
                            20170101);

and I have created the partition schema:

----------------------------------------------------------------------------
--- Create a Partition Scheme
----------------------------------------------------------------------------
use radhe
go
DROP PARTITION SCHEME PSC_OrderDateSID 
GO


CREATE PARTITION SCHEME PSC_OrderDateSID AS 
PARTITION PF_Facts_FactOrder_OrderDateSID   TO 
([F0],[F1], [F2], [F3], [F4], [F5], [F6], [F7]) 
GO

The table has a clustered index that does not include the partition column but was created as part of a PRIMARY KEY or UNIQUE constraint

My question is:

I can't make up my mind between these 2 options:

1)

Alter the clustered primary key adding the partition column to it

2)

change the primary key to NONCLUSTERED make this index CLUSTERED: Facts_FactOrder_OrderDateSID

what are the pros and cons of these 2 options? How would I go around to select which one serves this purpose in particular (need to improve the deletes based on the OrderDateSID column) ?

Answer 1

Based on your desire to improve deletes based on OrderDateSID (by possibly taking advantage of partition elimination), I don't see option 2 as an option at all.

All unique constraints involved in your 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 other words, your primary key (even defined as non-clustered cannot participate in this partitioning scenario because the base index definition does not include the partitioning key column.

If you tried to create the primary key against the partitioning scheme

ALTER TABLE [Facts].[FactOrder] ADD  CONSTRAINT [PK_FactOrder] PRIMARY KEY NONCLUSTERED 
(
    [FactOrderSID] ASC
)On PSC_OrderDateSID(OrderDateSID)
GO

you would receive this error

Msg 1908, Level 16, State 1, Line 3 Column 'OrderDateSID' is partitioning column of the index 'PK_FactOrder'. Partition columns for a unique index must be a subset of the index key. Msg 1750, Level 16, State 1, Line 3 Could not create constraint or index. See previous errors.

Now, you can certainly create a different partition function and scheme to support the partitioning of the primary key (to possibly help out in rebuilding indexes as the partition level), but it would not be aligned to the other partitions and thus would not allow you to do partition switching down the road - nor would it be involved in partition elimination because OrderDateSID is not part of the base index definition.

I think your only option is option 1.