SQL Server 2012 Filtered Index - Is the Where clause too complex
I'm trying to create the following filtered index: CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ( [MovedToODS] ASC ) INCLUDE ( [sub_id], [read_time] ) WHERE ([read_time]>='1/1/2012' OR [LastModifiedTime] > [MovedToODS]) I'm getting the following error: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'OR'. Is a Where clause like this not supported for filtered indexes? The online docs simply say the Where clause supports 'simple operations'. Is this too complex?
Yes, your WHERE clause is too complex; I don't believe OR is supported at all. You can't even do this: CREATE TABLE dbo.floob(id INT); CREATE INDEX x ON dbo.floob(id) WHERE id = 1 OR id = 2; In this case of course there is a supported alternative: CREATE INDEX x ON dbo.floob(id) WHERE id IN (1,2); That doesn't really help you. What you could do, though, is create two independent filtered indexes, if it makes sense for your workload to use them independently. Example: WHERE [read_time]>='20120101' AND [LastModifiedTime] <= [MovedToODS] ... WHERE [read_time]<'20120101' AND [LastModifiedTime] > [MovedToODS] Some decent references that talk about the various limitations: http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/ http://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/ http://www.sqlperformance.com/2013/04/t-sql-queries/filtered-indexes
SQL Server : sort based on current date for last 6 months
SQL Server: sort values Into LIKE columns with dynamic pivot
Conversion rate in SQL Server (subquery)
Resolve GROUP BY SQL more efficiently SQL Server AdventureWorks
The multi-part identifier could not be bound. Sql Server
Exporting table data to Excel 2013 using query in SQL Server 2012
is it possible to handle two different files that are stored in a single hard disk simultaneously?
SQL Server - Procedure to return results of dynamic Query
Timeout expire error when to insert data into table and simultaneously select query running on same table in SQL Server 2008
A stored procedure to call all SQL scripts in a folder
Get distinct rows except one column value
T-SQL : Cleaning up data, merging rows into columns
Using netbeans/Rest for html/SQL server
What is the difference between Lookup, Scan and Seek?
How do I get this SQL to return a 1 if no rows are found
Why is columnstore index not being used