Filtered Index can’t use like or functions in the where

Today I needed to create a filtered index to help a large update go faster. I wanted to use a like statement, but found out I could not use like in the where clause. Thanks to google; I found another way to make my update statement faster.

Filtered index I wanted:

CREATE NONCLUSTERED INDEX [INDX_Filter_IR_URI] ON [dbo].[IR_RecMedia]

([MediaURI] ASC,[RecordingDate] ASC)

INCLUDE ( [ExpirationDate])

Where MediaURI like ‘HTTP%’;

Received ERROR Message:

Msg 156, Level 15, State 1, Line 16

Incorrect syntax near the keyword ‘like’.


Decided to use a function to create the filtered index, but this also received an error.

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170329-200714] ON [dbo].[IR_RecMedia]

([MediaURI] ASC,[RecordingDate] ASC)

INCLUDE ( [ExpirationDate])

Where right(MediaURI, 4) = ‘HTTP’;

 

Received the Error:

Msg 10735, Level 15, State 1, Line 8

Incorrect WHERE clause for filtered index ‘NonClusteredIndex-20170329-200714’ on table ‘dbo.IR_RecMedia’.


How did I fix this issue:

I created an extra column on the table to store 1 (for true) or 0 (for false) based on if the MediaURI matched like ‘HTTP%’. Then I created an index on that column and RecordingDate. I had to change my update statement to use where HTTPbit = 1, instead of where MediaURI like ‘HTTP%’.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s