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: 
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])

```
From the TechNet Microsoft page

> 
> 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.
> 
> 
> 

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](https://techcommunity.microsoft.com/t5/sql-server-support/dealing-with-unique-columns-when-using-table-partitioning/ba-p/333995)