Filtered Index Syntax Error

Question

Post reply

Filtered Index Syntax Error

Jan Van der Eecken

SSCrazy Eights

Points: 9029
More actions
July 25, 2010 at 3:40 pm

#227240

Hi,
I'm having an issue with creating a filtered index. My table definition is as follows:
USE tempdb
GO
IF OBJECT_ID ( 'ABC', 'U' ) IS NOT NULL
DROP TABLE ABC
GO
CREATE TABLE ABC
(
ROW_ID int IDENTITY,
Col1 varchar(50) NOT NULL,
Col2 varchar(50) NOT NULL,
Col3 varchar(50) NOT NULL,
Col4 money ,
Col5 float,
Col6 varchar(50) NOT NULL,
Col7 money,
Col8 money,
Col9 money,
Col10 money,
Col11 money,
Col12 money,
Col13 money,
Col14 money
)
When I try to declare the filtered index as such:
CREATE NONCLUSTERED INDEX IX_MyFilteredIndex ON ABC
( ROW_ID )
INCLUDE
(
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14 )
WHERE Col1 = 'xyz'
AND ( Col2 = 'abc' or Col2 = 'something' )
GO
I get a syntax error:
Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'or'.
Any idea why? I am running SQL2K8 R2 (x86) by the way.
Regards,
Jan
--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

Try:

AND Col2 IN ('abc', 'something')

My guess is that the OR condition could result in a more complicated filter - Books Online only quotes examples using AND, IN and BETWEEN. If you hover over the error in SSMS, you get "Incorrect syntax near 'or'. Expecting AND, or ')'."

If you wanted to compare ( Col2 = 'abc' or Col3 = 'something'), then I think you're out of luck. Given that you're not including Col2 in your index, you wouldn't be able to identify exactly which row is being found. Most likely, the OR represents a difficulty in determining what is in the filtered index, which may prevent it being used in practice. Perhaps in the next release of SQL Server this will be possible.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Thanks Jim, the BETWEEN version works indeed. However, that doesn't really help me as it is beyond my control what WHERE clause is being thrown at me. Seems like taking advantage of filtered indexes is a dead-end in my case.

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 3

I suppose you will have to use the IN clause i.e.

.....WHERE Col1 = 'xyz'

AND Col2 in ( 'abc' ,'something' )

A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause is not allowed, only simple comparison operators are allowed.

check this article

http://www.mssqltips.com/tip.asp?tip=1785

Thanks,

Amit kulkarni

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 4

Thanks Jim, the BETWEEN version works indeed. However, that doesn't really help me as it is beyond my control what WHERE clause is being thrown at me. Seems like taking advantage of filtered indexes is a dead-end in my case.

Hi Jan,

You don't have control of the where clause? Are you trying to build your filtered index dynamically, perhaps on a temporary table?

If this is the case, you could just create a new temporary table or an indexed view with the filter you want, and use that in the same sort of way. It's a bit of a hack though.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

Amit kulkarni-491236 (7/26/2010)
I suppose you will have to use the IN clause i.e.
.....WHERE Col1 = 'xyz'
AND Col2 in ( 'abc' ,'something' )
A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause is not allowed, only simple comparison operators are allowed.
check this article
http://www.mssqltips.com/tip.asp?tip=1785
Thanks,
Amit kulkarni

I'm busy reading the article. Thanks for the link Amit.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 6

Jim McLeod (7/26/2010)
Thanks Jim, the BETWEEN version works indeed. However, that doesn't really help me as it is beyond my control what WHERE clause is being thrown at me. Seems like taking advantage of filtered indexes is a dead-end in my case.
Hi Jan,
You don't have control of the where clause? Are you trying to build your filtered index dynamically, perhaps on a temporary table?
If this is the case, you could just create a new temporary table or an indexed view with the filter you want, and use that in the same sort of way. It's a bit of a hack though.

Yes, I'm building the filtered index(es) on the fly, but on a permanent table. Ended up trying to build 1300-odd of them on one single 203 column table with 4.6 million rows. Doesn't work, I know, stupid idea, but we are just tossing ideas around amongst the team members on this project. I'll have a look at indexed views (which I believe can't be re-indexed on-line) on the most selective columns instead. Will keep you posted how it turns out.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]