All geek questions in one place

SQL Server Partition - Unique Index Error

I have a table divided by TRANSACTION_DATE_TIME.

The table has a column: ID.

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

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

but SQL says that "the partition column for a unique index must be a subset of the index key."

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

How to create an index without using the TRANSACTION_DATE_TIME column?

+6
source share
2 answers

Either you create a NON- partitioned index, or HAVE , to include the partitioning key in the partitioned index as follows:

Split 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

Undivided Index

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

Microsoft TechNet

When splitting a unique index (clustered or non-clustered), the separation column should be selected from those used in the unique index key. If a separation column is not possible, included in the unique key, you should use the DML trigger instead of ensuring uniqueness.

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

Work with unique columns when using table splitting

0
source

Source: https://habr.com/ru/post/904970/

More articles:


All Articles