>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.