Create unique index with filter expression having IS NULL or OR

Question

I am trying to develop a unique index.

CREATE UNIQUE NONCLUSTERED INDEX NCI_NewUnique
ON [NewUnique]([U1])
WHERE (ISNULL([MyField], '') = '')

My error is

Incorrect WHERE clause for filtered index 'NCI_NewUnique' on table 'NewUnique'.

Here is another attempt; This one I have removed the ISNULL(MyField, '') part. Why cannot this one have an OR?

CREATE UNIQUE NONCLUSTERED INDEX NCI_NewUnique
ON [NewUnique]([U1])
WHERE (
         ([MyId] IS NULL) 
         OR 
         ([MyId] IS NOT NULL AND [MyField] IS NOT NULL)
      )

Error is:

Incorrect syntax near the keyword 'OR'.

Mikael Eriksson · Accepted Answer · 2012-04-13 03:20:01Z

3

I don't know why but according to the documentation functions and or is not allowed.

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

answered Apr 13, 2012 at 3:20

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2019-12-31 09:21:29Z

0

I found this question facing a problem how do i make a filtered unique index with a ISNULL or OR in the filter expression. So while "that's not allowed" is a valid answer, it's not helpful for my case.

I did find an answer for my problem, though, so here it is for anyone else who's going to find it like me.

You can create a view with schemabinding, put your complex filter into it and create a unique clustered index for the view! It's a hack, but it works and the optimizer can even choose this index when querying the table.

More details here:

https://dba.stackexchange.com/questions/116347/unable-to-create-a-filtered-index-on-a-computed-column

edited Dec 31, 2019 at 9:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 17, 2019 at 11:12

Duke Garland

687 bronze badges

Collectives™ on Stack Overflow

Create unique index with filter expression having IS NULL or OR

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related