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

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

Categories

HOME
cil
semantic-ui
nuxeo
visual-studio-2013
facebook-graph-api
apache2
orientation
azureservicebus
laravel-4
histogrammar
jframe
gimp
desktop
unity2d
pjsip
cockroachdb
hashicorp-vault
spinnaker
image-recognition
automata
sendkeys
opera-mini
riak-ts
google-openid
ng2-dragula
k2
python-textprocessing
emv
dpi
xor
entity-system
clockwork
picturebox
construct-2
data-extraction
nunit-3.0
liquid-xml
installshield-2012
alchemy.js
ddms
android-maps-v2
lftp
encapsulation
onmouseover
aqgridview
ajp
istorage
persistent
maintenance
text-classification
tcserver
heroku-postgres
httplib2
pspice
build-process
manifest.mf
debugdiag
excon
dojox.charting
pagekit
soda
inotifypropertychanged
spatial-query
sun-codemodel
schtasks.exe
uialertview
js-cookie
subresource-integrity
abas
manjaro
digits
twitter-rest-api
mdm-zinc
moai
line-numbers
qtestlib
mser
ibmsbt
grunt-express
collabnet
ember-components
csplit
dache
emma
android-looper
ifft
dynamic-proxy
.net-cf-3.5
nimrod
dig
shortcuts
magickwand
ubuntu-11.10
radchart
fotoware
file-exists
userid
v4l
msbuildextensionpack
adsl
.app
zend-form-element
revision
method-signature
data-entry

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