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.
Best Answer
According to MSDN the IGNORE_DUP_KEY option doe's not apply on filtered indexes