I have designed a PurchaseOrder table – with partioning on CreatedDate column – In Visual Studion Data Tool as follow:
CREATE TABLE [dbo].[PurchaseOrder] (
[IdGlobal] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
[IdLocal] BIGINT NOT NULL,
[CreatedDate] DATE DEFAULT (getdate()) NOT NULL,
[ApprovedDate] DATE NULL,
[CreatorID] BIGINT NOT NULL,
[CompanyID] INT NOT NULL,
[PR_IdGlobal] UNIQUEIDENTIFIER NOT NULL,
[PartnerID] INT NOT NULL,
[DeliveryInfo] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
[Description] VARBINARY(MAX) NULL, -- Compressed Data
[CurrencyInfo] NVARCHAR(200) NOT NULL, --Json
[TrackingAccountID] INT NOT NULL,
[IsImport] BIT DEFAULT ((0)) NULL, -- Đây là đơn hàng nhập khẩu
[ListItems] VARBINARY(MAX) NULL, -- Compressed JsonList
[ListPaymentTempt] VARBINARY(MAX) NULL, -- Compressed JsonList
[ListItemIDs] NVARCHAR (200) NULL, --Json List of all ItemID in the ListItems -> For reports
[ListMeasureIDs] NVARCHAR (200) NULL, --Json List of all MeasureID in the ListItems -> For reports
[DeptApproval] VARBINARY(200) NULL, -- Compressed JsonList
[AccountantApproval] NVARCHAR(200) NULL, --Json
[DirectorApproval] NVARCHAR(200) NULL, --Json
[ValueInfo] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
[CommittionInfo] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
[StockInfo] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
[AcceptanceInfo] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
[PeopleReceiveAlert] VARBINARY(MAX) NOT NULL, -- Compressed JsonList
[Ref] VARBINARY(MAX) NOT NULL, -- Compressed JsonData
PRIMARY KEY CLUSTERED ([IdGlobal] ASC),
CONSTRAINT [FK_PO_CompanyID] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Company] ([Id]),
CONSTRAINT [FK_PO_CreatorID] FOREIGN KEY ([CreatorID]) REFERENCES [dbo].[Employee] ([IdGlobal]),
CONSTRAINT [FK_PO_idPR] FOREIGN KEY ([PR_IdGlobal]) REFERENCES [dbo].[PurchaseRequest] ([IdGlobal]),
CONSTRAINT [FK_PO_TrackingAccountID] FOREIGN KEY ([TrackingAccountID]) REFERENCES [dbo].TrackingAccount ([Id])
)
--Partion by CreatedDate (Into 120 partition: every 3 months)
ON [3MonthsRangeScheme](CreatedDate)
GO;
--Creat local Index for IdGlobal following partition CreatedDate
CREATE NONCLUSTERED INDEX [IX_PO_IdGlobal_Partition] ON [dbo].[PurchaseOrder] ([IdGlobal]) ON [3MonthsRangeScheme](CreatedDate)
GO
--Create Index for CreatedDate
CREATE NONCLUSTERED INDEX [IX_PO_CreatedDate_Partition] ON [dbo].[PurchaseOrder] ([CreatedDate])
GO
--Creat local Index for CompanyID following partition CreatedDate
CREATE INDEX [IX_PO_CompanyID_Partition] ON [dbo].[PurchaseOrder] ([CompanyID]) ON [3MonthsRangeScheme](CreatedDate)
GO
--Creat local Index for [CreatorID] following partition CreatedDate
CREATE INDEX [IX_PO_CreatorID_Partition] ON [dbo].[PurchaseOrder] ([CreatorID]) ON [3MonthsRangeScheme](CreatedDate)
GO
--Creat local Index for [PartnerID] following partition CreatedDate
CREATE INDEX [IX_PO_PartnerID_Partition] ON [dbo].[PurchaseOrder] (PartnerID) ON [3MonthsRangeScheme](CreatedDate)
GO
--Creat local Index for [PartnerID] following partition CreatedDate
CREATE INDEX [IX_PO_PR.IdGlobal_Partition] ON [dbo].[PurchaseOrder] (PR_IdGlobal) ON [3MonthsRangeScheme](CreatedDate)
GO
--Creat local Index for [TrackingAccountID] following partition CreatedDate
CREATE INDEX [IX_PO_PR.TrackingAccountID_Partition] ON [dbo].[PurchaseOrder] (TrackingAccountID) ON [3MonthsRangeScheme](CreatedDate)
GO
But went I publish, It’s alway display error message like this:
Creating Table [dbo].[PurchaseOrder]... (270,1): SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1908, Level 16, State 1, Line 1
Column 'CreatedDate' is partitioning column of the index
'PK__PurchaseOrder__8860F3A5'. Partition columns for a unique index
must be a subset of the index key. (270,0): SQL72045: Script execution
error. The executed script: CREATE TABLE [dbo].[PurchaseOrder] (
[IdGlobal] UNIQUEIDENTIFIER NOT NULL,
[IdLocal] BIGINT NOT NULL,
[CreatedDate] DATE NOT NULL,
[ApprovedDate] DATE NULL,
[CreatorID] BIGINT NOT NULL,
[CompanyID] INT NOT NULL,
[PR_IdGlobal] UNIQUEIDENTIFIER NOT NULL,
[PartnerID] INT NOT NULL,
[DeliveryInfo] VARBINARY (MAX) NOT NULL,
[Description] VARBINARY (MAX) NULL,
[CurrencyInfo] NVARCHAR (200) NOT NULL,
[TrackingAccountID] INT NOT NULL,
[IsImport] BIT NULL,
[ListItems] VARBINARY (MAX) NULL,
[ListPaymentTempt] VARBINARY (MAX) NULL,
[ListItemIDs] NVARCHAR (200) NULL,
[ListMeasureIDs] NVARCHAR (200) NULL,
[DeptApproval] VARBINARY (200) NULL,
[AccountantApproval] NVARCHAR (200) NULL,
[DirectorAp (270,1): SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1750, Level 16, State 1, Line 1 Could not create
constraint or index. See previous errors. (270,0): SQL72045: Script
execution error. The executed script: CREATE TABLE
[dbo].[PurchaseOrder] (
[IdGlobal] UNIQUEIDENTIFIER NOT NULL,
[IdLocal] BIGINT NOT NULL,
[CreatedDate] DATE NOT NULL,
[ApprovedDate] DATE NULL,
[CreatorID] BIGINT NOT NULL,
[CompanyID] INT NOT NULL,
[PR_IdGlobal] UNIQUEIDENTIFIER NOT NULL,
[PartnerID] INT NOT NULL,
[DeliveryInfo] VARBINARY (MAX) NOT NULL,
[Description] VARBINARY (MAX) NULL,
[CurrencyInfo] NVARCHAR (200) NOT NULL,
[TrackingAccountID] INT NOT NULL,
[IsImport] BIT NULL,
[ListItems] VARBINARY (MAX) NULL,
[ListPaymentTempt] VARBINARY (MAX) NULL,
[ListItemIDs] NVARCHAR (200) NULL,
[ListMeasureIDs] NVARCHAR (200) NULL,
[DeptApproval] VARBINARY (200) NULL,
[AccountantApproval] NVARCHAR (200) NULL,
[DirectorAp An error occurred while the batch was being executed.
Please tell me where is my mistake in the code?
2
Answers
The error message you provided is related to partitioning a unique index. When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key. This restriction enables SQL Server to investigate only a single partition to make sure no duplicate of a new key value already exists in the table. (Reference : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bcc888d-2f5b-4a6a-99b0-e4a6bf82277d/parition-column-for-a-unique-index-must-be-a-subset-of-index-key?forum=transactsql
The error message indicates that the
CreatedDate
column is used as a partitioning column. But for the indexPK__PurchaseOrder__8860F3A5
, it is not part of the index key. The partitioning column must be part of the index key for a unique index.To fix this error: While creating
purchaseorder
table, you can addCreatedDate
along with theIDglobal
column in the primary key cluster.