Microsoft – SQLServer – Partitioning – Index – Unique

When trying to create a unique clustered index on a partitioned table, we received this error:

Msg 1908, Level 16, State 1, Line 7
Column ‘CREATED’ is partitioning column of the index ‘S_EVT_ACT_P1′. Partition columns for a unique index must be a subset of the index key.

The create table statement is:


CREATE TABLE [dbo].[S_EVT_ACT]
(
      [ROW_ID] [nvarchar](15) NOT NULL
    , [CREATED] [datetime] NOT NULL
    , [CREATED_BY] [nvarchar](15) NOT NULL
)
ON partitionScheme_Year -- partition
([CREATED]); -- partition column

The create index statement is:


CREATE UNIQUE CLUSTERED INDEX [S_EVT_ACT_P1]
ON [dbo].[S_EVT_ACT]
( [ROW_ID] ASC)
ON partitionScheme_Year([CREATED]);


MS Literature explains the restriction as

When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key.

Note:

This restriction enables SQL Server to investigate only a single partition to make sure no duplicate of a new key value already exists in the table.  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 the suggestested remedy are:


CREATE UNIQUE CLUSTERED INDEX [S_EVT_ACT_P1]
on [dbo].[S_AVT_ACT]
    ( [ROW_ID], [CREATED])
ON partitionScheme_Year -- partition
    ([CREATED]); -- partition column

or



CREATE UNIQUE CLUSTERED INDEX [S_EVT_ACT_P1]
on [dbo].[S_AVT_ACT]
    ( [CREATED], [ROW_ID])
ON partitionScheme_Year -- partition
    ([CREATED]); -- partition column

Obvious using either of these options breaks our intent to ensure that ROW_ID is unique.

If we try to keep ROW_ID as unique and use CREATED as part of the include CLAUSE:



CREATE UNIQUE CLUSTERED INDEX [S_EVT_ACT_P1]
on [dbo].[S_AVT_ACT]
(  [ROW_ID])
include
([CREATED])
ON partitionScheme_Year -- partition
([CREATED]); -- partition column

We get the error pasted above:

Msg 10601, Level 16, State 1, Line 1
Cannot specify included columns for a clustered index.

We also tried using a primary key, rather than the unique index:

ALTER TABLE [dbo].[x_S_EVT_ACT]
    ADD CONSTRAINT [PK_x_S_EVT_ACT]
    PRIMARY KEY
      ([ROW_ID]) ;

Msg 1908, Level 16, State 1, Line 1
Column ‘CREATED’ is partitioning column of the index ‘PK_x_S_EVT_ACT’.
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.


In conclusion, we are not so sure how to create “truly” unique indexes on partitioned tables.  By that, we mean unique indexes that only contain the columns that are intended to be unique.

References:

  1. Special Guidelines for Partitioned Indexes
    http://msdn.microsoft.com/en-us/library/ms187526.aspx

  2. Local Index Issue With Partitioned PK and Unique Key Constraints
    http://richardfoote.wordpress.com/2007/12/20/local-index-issue-with-partitioned-pk-and-unique-key-constraints/

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s