Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtered index "AND" and "OR" Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 3:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 57, Visits: 267
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
Post #1596739
Posted Monday, July 28, 2014 6:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 2,078, Visits: 3,616
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.
Post #1596797
Posted Tuesday, July 29, 2014 12:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 57, Visits: 267
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
Post #1597158
Posted Tuesday, July 29, 2014 2:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 2,078, Visits: 3,616
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
Post #1597192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse