CODEWITHSUNDEEP
sqlsql-server-2008
Sin5k4
Sin5k4

Reputation: 1626

Conditional Indexing using multiple values

CREATE UNIQUE NONCLUSTERED INDEX [erm_UXAGC_WORKINGDRAWINGID] 
ON [dbo].[INVENTTABLE] ([AGC_WORKINGDRAWINGID] ASC)
WHERE ([AGC_WORKINGDRAWINGID] NOT IN ('', 'UR-GE'))

Is this type of conditional indexing possible? From the docs, it says sql doesn't support OR syntax and you have to use IN, however I could not get this config to work as well, I get syntax error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'NOT'.

Upvotes: 1

Views: 92

Answers (1)

sstan
sstan

Reputation: 36483

There seems to be a bit of confusion as to what is permitted when creating filtered indexes, as the official documentation is not very clear about this.

Case in point, the following bug report: Filtered index creation failed with NOT IN clause. The interesting part is MSFT's response:

Hi, Thanks for your feedback regarding the Filtered Indexes feature. In SQL Server 2008, filtered indexes will support a limited set of simple where clauses only. The exact grammar of supported filter predicates are listed in the BOL. We currently do not support the NOT IN clause due to the optimizer matching difficulties for such clauses. As you have noted IN clause is supported. We will continue to evaluate the supported set of predicates and enhance them in the future releases.

The above comment was written regarding SQL Server 2008. However, as far as I can tell, it still holds true with the most recent versions of SQL Server.

EDIT: Workaround

That same bug report includes a workaround that you can use:

WHERE [AGC_WORKINGDRAWINGID] <> '' AND [AGC_WORKINGDRAWINGID] <> 'UR-GE'

Upvotes: 2

Related Questions