Creating Filtered Index with OR Statement

Question

Post reply

Creating Filtered Index with OR Statement

dha82

SSC Enthusiast

Points: 190
More actions
February 19, 2016 at 3:43 am

#399744

Hi all,
i am trying to create a filtered index based on a datetime - field and i am getting a syntax error when i tried to run the Statement:
CREATE INDEX TestIndex ON "MyDB"."dbo"."My Table" ("My DateTime Field")
WHERE ("My DateTime Field" = '1753-01-01 00:00:00' OR "My DateTime Field" >= '2016-01-01 00:00:00')
The result is:
Error Message: Incorrect syntax near the keyword 'OR'
Any ideas what i am doing wrong or how to solve this issue ?
Thank you in advance.
Dennis

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Filters in indexes don't allow OR.

I'm not sure if what you're trying to do can be done in a filtered index. They have to be simple filters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

What Gail says. Filtered indexes only allow AND and IN. OR is not allowed. Neither is NOT, which would otherwise have been a nice workaround.

An OR list of distinct values can be replaced with IN, your OR condition cannot use that trick.

I am guessing that 1753-1-1 is used to represent "no date" in your table. Can you change the application to use a distant future date (e.g. 9999-1-1) instead?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And keep in mind that for a query to use a filtered index, the filter in the query WHERE clause has to match or be an easily determined subset of the index's filter

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dha82 SSC Enthusiast Points: 190 More actions · Answer 4

Thanks for you replies.

The idea to set the null value to 9999-1-1 is very good, i'll ask our database administrator to change the application this way.

Best regards,

Dennis

Bill Talada SSChampion Points: 11954 More actions · Answer 5

I often create two filtered indexes when I have a need for an OR.

Filtered indexes are great when you need to enforce uniqueness on a nullable column.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

Bill Talada (2/19/2016)
I often create two filtered indexes when I have a need for an OR.

What does that accomplish?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Bill Talada SSChampion Points: 11954 More actions · Answer 7

Multiple filtered indexes are perfect for handling different types of work queues for a table. When there is a work flow and rows change through many statuses each user may be working on a specific status. Obviously their SELECT will be filtered for their status. Trying to handle several different cases with an IN() will often prevent the optimizer from using any filtered index, at least for me it has. Separate filtered indexes also more easily allows for more specific filtering on multiple columns.

In the case above, the min datetime is probably an indicator of a new row while the actual dates in other rows probably means a status of validated or approved or something other than new. Likely different users will be processing those cases separately.

aaron.reese SSChampion Points: 13498 More actions · Answer 8

Slightly OT but 'no date' as NULL, '1753-01-01' or '9999-01-01' will have different performance impacts depending on your database collation and indexes. My understanding is that under most of the common collations, NULLs appear at the start of the index so having NULL or 'earlyDate' as no date and then populating it causes the record pointer in the index to move leading to page fragmentation and a degrading performance. In this instance replacing these with 'lateDate' will likely cause less index fragmentation. If the date is part of the clustered key then this is even more important as you will be actually moving the entire record to a new page rather then just the index pointer. but please CHECK YOUR COLLATION before you make this change.

The change could also affect a large number of reports and internal code if it is looking for dates > X; currently these are not picked up and a change to 'lateDate' will suddenly see them. You might also want to flush the statistics and query plans if you make this change.