SQL Server Partitioning - Unique Index Error

Question

I have a table that is partitioned by TRANSACTION_DATE_TIME.

Table has a column: ID.

I want to create a unique index for ID on partition scheme 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 "Partition column for a unique index must be a subset of index key".

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

How can I create the index without using TRANSACTION_DATE_TIME column?

Answer 1

You have two choices: you create a NON-partitioned index, or you MUST include the partitioning key in a partitioned index:

• Non-partitioned index

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

OR

• Partitioned index that includes the partitioning key

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