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
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) ?
Best Answer
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
you would receive this error
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.