sql-server


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

Related Links

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

Categories

HOME
sql-server-2008
swift
converter
cluster-computing
ember.js
amazon-swf
smartphone
mapbox-gl-js
codeeffects
sbt-assembly
webdav
ll
java-home
apiconnect
websphere-liberty
gimp
aruco
facebook-javascript-sdk
uiactivityviewcontroller
ndis
viber
rebol2
arm-template
zoho
jconsole
thingsboard
hhvm
avplayeritem
dspic
continuous-deployment
rhmap
configure
referenceerror
twitter-bootstrap-2
android-vpn-service
apache-cloudstack
preg-grep
edb
geopy
data-extraction
service-locator
forecasting
strstr
sql-like
infusionsoft
dart-pub
mediawiki-extensions
stereo-3d
intersystems-ensemble
ttcn
ivona
dojox.grid.datagrid
vcf
taskmanager
livefyre
xmgrace
etherpad
info
heroku-postgres
pdfkit
createobject
windows-azure-pack
chessboard.js
mix
foxit
unsatisfiedlinkerror
uwp-maps
distribute
spinlock
handlebars.java
loopj
pyrocms
calibration
ubercart
criteria-api
ibm-data-studio
adobe-indesign
ember-cli-addons
client-side-validation
temp-tables
jquery-autocomplete
ghostdoc
author
humanizer
getimagedata
jstack
mbox
unison
bsod
nsmatrix
ng-hide
multiple-login
dig
argb
nuspec
gdataxml
domain-calculus
sequelpro
expression-evaluation
nemerle
tcxgrid
gethashcode
nvelocity
pureftpd
gwt-2.2-celltable
scala-2.8
linfu
filetime
windows-controls
virtualquery
document-library
associativity

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App