Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON

by Manu Roitman   Last Updated July 23, 2017 16:06 PM

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.



Answers 2


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

Shimon Gb Gibraltar
Shimon Gb Gibraltar
February 08, 2016 08:40 AM

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.

Andriy M
Andriy M
February 08, 2016 09:23 AM

Related Questions



SQL Server 2008 - Question about index behaviour

Updated June 16, 2015 12:02 PM


Temp Table Filtered Index Creation Blocked

Updated September 14, 2018 00:06 AM