>Hi,
>
>Is it possible and if yes, how, to create a filtered index on Not Empty value? For example, I tried the following:
>
>
>CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptEmpty]
>ON [MYTABLE] (EMAIL_ADDR)
>WHERE LEN(EMAIL_ADDR) > 0
>
>
>But I get error "Incorrect where clause." What is wrong with my syntax?
Apparently it's a limitation of the where clause. This sort of worked for me
CREATE UNIQUE INDEX [UniqueExceptEmpty]
ON dbo.EmailAddresses (EmAddress)
WHERE EmAddress <> ''
(It gave an error about uniqueness as I probably have 2 same addresses).
In the Limitations & Restrictions section in BOL you can read
Filtered indexes are defined on one table and only support
simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.
If it's not broken, fix it until it is.
My Blog