I'm trying to create a partitioned table where partitioning is determined by OrderDate
. I'm trying to create a clustered index
on OrderDate
and a nonclustered primary key
on OrderID
. But I get an error saying:
Msg 1908, Level 16, State 1, Line 1 Column 'OrderDate' is partitioning
column of the index 'PK_OrderID'. Partition columns for a unique index
must be a subset of the index key. Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How to modify this to get it work:
CREATE TABLE dbo.Orders
(
OrderID INT NOT NULL ,
Name NVARCHAR(20) NULL ,
OrderDate DATE NOT NULL ,
)
ON PartSchemeOrders(OrderDate)
CREATE CLUSTERED INDEX IX_OrderDate
on dbo.Orders (OrderDate)
on PartSchemeOrders(OrderDate)
ALTER TABLE dbo.Orders
ADD CONSTRAINT PK_OrderID PRIMARY KEY NONCLUSTERED (OrderID)
Best Answer
As the error message says, any partition-aligned unique index has to include the partitioning key in the index key. This requirement exists so the engine can enforce uniqueness on updates without checking every partition.
In your case, this means including
OrderDate
in the nonclustered index key, or having a non-aligned index. Both are potentially valid choices, depending on your circumstances. To preserve alignment, your table and index definitions would be:Of course this changes the uniqueness that the nonclustered index enforces. Now, only the combination of
OrderID
andOrderDate
is guaranteed to be unique. It is theoretically possible to add duplicateOrderID
s, so long as theOrderDate
is different. Whether this change of semantic is acceptable to you depends on your circumstances, but it is something to be aware of.The alternative is to have the nonclustered primary key non-aligned:
This preserves the uniqueness of
OrderID
alone, and has some benefits with queries that computeMIN
orMAX
aggregates using the index, but you lose the ability toSWITCH
partitions in and out without dropping the primary key and recreating it after the switching operation.You can read more about partitioning in this section of Books Online and more on the issues of aligned and non-aligned indexes in this excellent answer by Remus Rusanu.