Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtered index "AND" and "OR"


Filtered index "AND" and "OR"

Author
Message
DennisPost
DennisPost
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 536
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
(
SurroID Int IDENTITY PRIMARY KEY,
CompID SmallInt,
VehReg Varchar(15),
CodeNr TinyInt,
SentDate DateTime,
RcvdDate AS (DATEADD(S, 1, SentDate)) ,
Meter Int,
Ver1 TinyInt,
Ver2 TinyInt
)

INSERT FilterTbl
SELECT TOP 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
FROM sys.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
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2859 Visits: 5320
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
DennisPost
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 536
Thanks for the reply Gazareth.

Jes Schultz Borland's 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
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2859 Visits: 5320
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> | <comparison>

<disjunct> ::=
column_name IN (constant ,...n)

<comparison> ::=
column_name <comparison_op> constant

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

Cheers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search