skip to Main Content

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


  1. 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

    Login or Signup to reply.
  2. The error message indicates that the CreatedDate column is used as a partitioning column. But for the index PK__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 add CreatedDate along with the IDglobal column in the primary key cluster.

    CREATE TABLE [dbo].[PurchaseOrder] (
        [IdGlobal]                 UNIQUEIDENTIFIER     NOT NULL DEFAULT NEWID(),
        [IdLocal]                  BIGINT               NOT NULL,
        [CreatedDate]              DATE                 DEFAULT (getdate()) NOT NULL,
        [ApprovedDate]             DATE                 NULL,
    ........
    .........
    
     PRIMARY KEY CLUSTERED ([IdGlobal] ASC,[CreatedDate]  ),
     ......
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search