SQL Server分区表报错：唯一索引分区列需为索引键子集

问题背景 #

我在Visual Studio Data Tool中设计了一张以CreatedDate列为分区依据的PurchaseOrder表，对应的SQL代码如下：

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

错误信息 #

发布时始终出现如下错误：

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.

问题定位与解决方案 #

错误原因 #

SQL Server分区表规则明确：当唯一索引（包括主键）所在的表使用分区方案时，分区列必须是该唯一索引键列的子集。你的主键PK__PurchaseOrder__8860F3A5仅基于IdGlobal，而表的分区列是CreatedDate，两者无包含关系，因此触发错误。

解决方法 #

有两种可行处理方案：

方案一：将分区列添加到主键键列中

修改主键定义，把CreatedDate加入主键键列集合。由于IdGlobal是UNIQUEIDENTIFIER类型已全局唯一，添加CreatedDate不会影响唯一性，同时满足分区规则：

PRIMARY KEY CLUSTERED ([IdGlobal] ASC, [CreatedDate] ASC),

方案二：将主键指定到默认文件组（不分区）

若不想修改主键结构，可让主键不参与分区，放在默认的PRIMARY文件组，表本身仍使用分区方案：

PRIMARY KEY CLUSTERED ([IdGlobal] ASC) ON [PRIMARY],

表的分区定义保持不变：

ON [3MonthsRangeScheme](CreatedDate)

注意：此方案会导致主键所在的聚簇索引不随分区列拆分，可能削弱分区表的性能优势，需根据业务场景权衡。

额外优化建议 #

非唯一索引IX_PO_CreatedDate_Partition未指定分区方案，建议统一使用与表相同的分区方案，保持索引和表的分区一致性，提升跨分区查询性能：

CREATE NONCLUSTERED INDEX [IX_PO_CreatedDate_Partition] ON [dbo].[PurchaseOrder] ([CreatedDate]) ON [3MonthsRangeScheme](CreatedDate)
GO

内容的提问来源于stack exchange，提问作者Le Anh Xuan