Filtered index "AND" and "OR"

  • 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

  • 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.

  • 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

  • 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

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

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