SQL Server分区 - 唯一索引错误

时间:2022-12-30 16:05:11

I have a table that is partitioned by TRANSACTION_DATE_TIME.

我有一个由TRANSACTION_DATE_TIME分区的表。

Table has a column: ID.

表有一列:ID。

I want to create a unique index for ID on partition scheme as:

我想在分区方案上为ID创建唯一索引:

CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] 
(
    [ID] ASC
) ON [PS_DATETIME_WEEKLY]([TRANSACTION_DATE_TIME])

but SQL says "Partition column for a unique index must be a subset of index key".

但SQL说“唯一索引的分区列必须是索引键的子集”。

I really don't need TRANSACTION_DATE_TIME column in this index.

我在这个索引中真的不需要TRANSACTION_DATE_TIME列。

How can I create the index without using TRANSACTION_DATE_TIME column?

如何在不使用TRANSACTION_DATE_TIME列的情况下创建索引?

2 个解决方案

#1


8  

Or you create NON-partitioned index, or you HAVE to include the partitioning key into partitioned index like this:

或者您创建了非分区索引,或者您必须将分区键包含在分区索引中,如下所示:

Partitioned index

CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] 
(
    [ID] ASC,
    TRANSACTION_DATE_TIME
) ON [PS_DATETIME_WEEKLY]([TRANSACTION_DATE_TIME])

OR

Non-partitioned index

CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] 
(
    [ID] ASC
) ON PRIMARY

#2


0  

From the TechNet Microsoft page

从TechNet Microsoft页面

When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key. If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness.

分区唯一索引(群集或非群集)时,必须从唯一索引键中使用的分区列中选择分区列。如果分区列不可能包含在唯一键中,则必须使用DML触发器来强制执行唯一性。

So to implement a workaround you can check this link out... one workaround from the blog and another one in the comments

因此,要实施变通方法,您可以检查此链接...博客中的一种解决方法和评论中的另一种解决方法

Dealing with Unique Columns when Using Table Partitioning

使用表分区时处理唯一列

#1


8  

Or you create NON-partitioned index, or you HAVE to include the partitioning key into partitioned index like this:

或者您创建了非分区索引,或者您必须将分区键包含在分区索引中,如下所示:

Partitioned index

CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] 
(
    [ID] ASC,
    TRANSACTION_DATE_TIME
) ON [PS_DATETIME_WEEKLY]([TRANSACTION_DATE_TIME])

OR

Non-partitioned index

CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] 
(
    [ID] ASC
) ON PRIMARY

#2


0  

From the TechNet Microsoft page

从TechNet Microsoft页面

When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key. If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness.

分区唯一索引(群集或非群集)时,必须从唯一索引键中使用的分区列中选择分区列。如果分区列不可能包含在唯一键中,则必须使用DML触发器来强制执行唯一性。

So to implement a workaround you can check this link out... one workaround from the blog and another one in the comments

因此,要实施变通方法,您可以检查此链接...博客中的一种解决方法和评论中的另一种解决方法

Dealing with Unique Columns when Using Table Partitioning

使用表分区时处理唯一列