>>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?
>
>You cannot use a function on a where:
http://msdn.microsoft.com/en-ca/library/ms188783.aspx>
>WHERE filter_predicate
>Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.
>The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
Thank you for the link and the explanation.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
