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
Replication advice for local SQL Server to hosted server
How do I select columns together with aggregate functions?
SQL Server / .NET - Get the connection pool size
Amazon RDS SQL Server Change Database Owner
Convert Access query into SQL Server stored procedure
How to Add Entity Without Recreating Tables
SQL Server TOP constraint [closed]
How to convert 71632.0638353154 to 71,632.06 in SQL Server and SYBASE?
I want to fetch autoincremented varchar values from SQL Server 2008
Copy and paste excel to sql server table
C#.net Console Application SQLCommand.CommandTimeOut property not working
Is there a limit to the number of Common Table Expression (CTE) expressions allowed?
Lightswitch : can it create a real time SQLServer DB Monitoring application?
Most Efficient Many-To-Many Linq Query
Repeated Name/Password Requests Using MS Access 2003 and SQL Server 2008
Why does a query using a parameter return no rows when the same query with constants returns 122K