Filtered index "AND" and "OR"

Question

Post reply

Filtered index "AND" and "OR"

DennisPost

SSCrazy

Points: 2691
More actions
July 28, 2014 at 3:33 am

#293702

I am trying to create an index for a specific query that is used a lot.
Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)
Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.
None of which gave me a clear answer.
How can I make an index for this query?
Here's the code setup
------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,
VehRegVarchar(15),
CodeNrTinyInt,
SentDateDateTime,
RcvdDateAS (DATEADD(S, 1, SentDate)) ,
MeterInt,
Ver1TinyInt,
Ver2TinyInt
)
INSERTFilterTbl
SELECTTOP 1000 -- 4.6 mil rows in table
CompID= ABS(CHECKSUM(NEWID()) % 5000) ,
VehReg= CASE ABS(CHECKSUM(NEWID()) % 10)
WHEN 0 THEN 'Walker_1'
WHEN 1 THEN 'Walker_2'
WHEN 2 THEN 'Walker_3'
ELSE 'Something else'
END ,
CodeNr= ABS(CHECKSUM(NEWID()) % 50) + 1,
SentDate= DATEADD(MS, ABS(CHECKSUM(NEWID()) % 5000), GETDATE()),
Meter= CASE ABS(CHECKSUM(NEWID()) % 5)
WHEN 0 THEN '0'
ELSE ABS(CHECKSUM(NEWID()) % 100000)
END ,
Ver1= CASE ABS(CHECKSUM(NEWID()) % 5)
WHEN 0 THEN '0'
ELSE ABS(CHECKSUM(NEWID()) % 20)
END ,
Ver2= CASE ABS(CHECKSUM(NEWID()) % 5)
WHEN 0 THEN '0'
ELSE ABS(CHECKSUM(NEWID()) % 20)
END
FROMsys.All_Columns C1, sys.All_Columns C2
------------------------------------------------------------------------------------------
--- The query
------------------------------------------------------------------------------------------
SELECT [SurroID]
,[CodeNr]
,[VehReg]
,[CompID]
,[SentDate]
,ROW_NUMBER() OVER(PARTITION BY [CompID], [VehReg] ORDER BY [SentDate], [RcvdDate] DESC) AS RowNr
FROM[dbo].[FilterTbl]
WHERE(
Meter = 0
AND Ver1 = 0
AND Ver2 = 0
)
OR VehReg LIKE 'Walker%'
------------------------------------------------------------------------------------------
-- The indexes
------------------------------------------------------------------------------------------
-- Gets ignored
CREATE NONCLUSTERED INDEX NCI_FilterTbl_MeterV1V2VehReg_Incl_20140728 ON FilterTbl
(
Meter,
Ver1,
Ver2,
VehReg
)
INCLUDE (CompID, CodeNr, SentDate, RcvdDate)
-- Incorrect syntax near the keyword 'OR'. (Have tried many different variations)
CREATE NONCLUSTERED INDEX NCI_FilterTbl_CompIDVehRegSentRcvd_Incl_Filtered_20140728 ON FilterTbl
(
CompID,
VehReg,
SentDate,
RcvdDate
)
INCLUDE (CodeNr)
WHERE
(
(
Meter = 0
AND Ver1 = 0
AND Ver2 = 0
)
OR VehReg LIKE 'Walker%'
)
------------------------------------------------------------------------------------------
-- Clean up
------------------------------------------------------------------------------------------
-- DROP TABLE FilterTbl
Cheers

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

You can't use OR in the where clause of a filtered index.

The non-filtered one is ignored as having VehReg in the index key isn't helpful to that query.

An indexed view may serve you better here. Note there are caveats to using them:

http://msdn.microsoft.com/en-us/library/ms191432(v=sql.105).aspx

Or try separate indexes - one on (Meter, Ver1, Ver2) and the other on VehReg, both with the included columns SurroID, CodeNr, VehReg, CompID, SentDate.

Might be a bit hit and miss as to whether the optimiser uses both indexes though.

DennisPost SSCrazy Points: 2691 More actions · Answer 2

Thanks for the reply Gazareth.

Jes Schultz Borland's[/url] article says you can have OR in filtered indexes, but in the comments Aaron Bertrand corrects this. Apart from your comment I have not found any other documentation saying OR is not allowed in filtered indexes.

Do you perhaps know of a link?

In the mean time, I'll follow up on your indexed view suggestion.

Cheers

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

No problem.

It's not made clear, but it is on the MSDN page for CREATE INDEX http://msdn.microsoft.com/en-gb/library/ms188783(v=sql.105).aspx

The important lines are below, note in the filter_predicate part only AND is allowed, not OR.

You can see from the comparison_op list that LIKE isn't allowed either.

[ WHERE <filter_predicate> ]

...

<filter_predicate> ::=

<conjunct> [ AND <conjunct> ]

<conjunct> ::=

<disjunct> ::=

column_name IN (constant ,...n)

<comparison> ::=

column_name <comparison_op> constant

<comparison_op> ::=

{ IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

Cheers