3

I have a table , in SQL Server 2012 enterprise edition, for which I want to have a unique index with IGNORE_DUP_KEY = ON, however I only want this index (and ignoring the duplicates) to apply only to a subset of the table - only rows with FLAG = 1.

CREATE TABLE [dbo].[FOO]
([ID] NOT NULL,
[Value] [int] NULL,
[FLAG] [bit] NULL)

When trying to create the index using below query

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UQ_FL_FOO_VALUE] ON [dbo].[FOO]
([Value] ASC)
WHERE [flag] = 1
WITH (IGNORE_DUP_KEY = ON)

I get an error:

Msg 10618, Level 16, State 1, Line 1 Cannot create filtered index 'IDX_UQ_FL_FOO_VALUE' on table 'dbo.FOO' because the statement sets the IGNORE_DUP_KEY option to ON. Rewrite the statement so that it does not use the IGNORE_DUP_KEY option.

Is it not possible to combine a filtered unique index with the IGNORE_DUP_KEY = ON?

Setting the index to non-unique is not an option. I need it to be unique to filter duplicate inserts, but only when inserting values with FLAG = 1. I need the insert of a duplicated row to be ignored, not to fail.

  • 1
    This is rather insane. Say I try to insert 5 rows with same [Value], all with FLAG=1. You want the 4 rows to be ignored and only 1 row to succeed being inserted? And all these silently, without any warning or error? And which one of the 5 to be chosen to succeed? A random one of the 5? – ypercubeᵀᴹ Feb 8 '16 at 9:20
2

According to MSDN the IGNORE_DUP_KEY option doe's not apply on filtered indexes

6

As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual:

Filtered indexes do not allow the IGNORE_DUP_KEY option.

There is a workaround, however, if you can afford a small change to the table's structure. With the help of a uniquifier column you can create a unique index that would effectively allow you to have unique Values only for rows with FLAG = 1.

The following assumes that the ID column is already declared as unique (either being the primary key or having a UNIQUE constraint on it). First, add a computed column that would evaluate to NULL for the FLAG = 1 rows and to ID for all the others:

ALTER TABLE dbo.FOO
ADD Uniquifier AS CASE FLAG WHEN 1 THEN NULL ELSE ID END;

Now you can create a unique index on (Value, Uniquifier) with the IGNORE_DUP_KEY option:

CREATE UNIQUE NONCLUSTERED INDEX UQ_1
ON dbo.FOO (Value, Uniquifier)
WITH (IGNORE_DUP_KEY = ON);

Since Uniquifier will be NULL for rows with FLAG = 1, the uniqueness will be determined based on Value alone. All the other rows will be unique regardless of Value because Uniquifier is unique (since its value is taken from a unique column, ID).

A live demonstration of the method is available at SQL Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.