is there a work around for when you want to put an OR inside a filtered index?

Question

is there a work around for when you want to put an OR inside a filtered index?

create index FIDX_tblbOrders_sdtmOrdCreated_INCL 
on dbo.tblBOrder(sdtmOrdCreated)
INCLUDE (sintMarketID,
         strCurrencyCode,
         sintOrderStatusID
         )
WHERE ((sintMarketId=1)
AND ( (sintOrderStatusId < 9) OR (sintOrderStatusId > 14)))

I am trying to create the index above, because I am NOT interested in any situation where sintOrderStatusId IN (9-14)

Of course I can create a view or indexed view, but I was trying to avoid that.

just adding more info: sintOrderStatusId is a smallint NOT NULL and the possible values range from 1 to 30. the 9 to 14 are to be avoided, therefore the filtered index.

Answer 1

Unfortunately, there seems to be no way to create a negative filter for an index, without resorting to creating a materialized view. If it were possible to create a negative filter such as the one you'd like, it would be quite difficult for the query-optimizer to "pick" the index for use, drastically increasing the time required to find a good plan.

Depending on the query patterns for this table, you could simply create two indexes; one for less than 9 and one for greater than 14. Either of these indexes may be chosen by the query optimizer for simple WHERE clauses such as WHERE StatusID = 6

CREATE TABLE dbo.TestNegativeFilter
(
    TestNegativeFilter INT NOT NULL
        CONSTRAINT PK_TestNegativeFilter
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusID INT NOT NULL
);
GO

CREATE INDEX IX_TestNagativeFilter_LessThan9
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID < 9);

CREATE INDEX IX_TestNagativeFilter_GreaterThan14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID > 14);

Another way to accomplish this might be:

CREATE INDEX IX_TestNegativeFilter_9_to_14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID IN (9, 10, 11, 12, 13, 14));

SELECT *
FROM dbo.TestNegativeFilter tnf
EXCEPT
SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID IN (9, 10, 11, 12, 13, 14);

This uses the index filtered on 9 to 14 to exclude rows.

On my test rig, a simple covering index returns rows by far the quickest:

CREATE NONCLUSTERED INDEX IX_TestNegativeFilter_StatusID
ON dbo.TestNegativeFilter(StatusID)
INCLUDE (TestNegativeFilter);

SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID NOT IN (9, 10, 11, 12, 13, 14);

---

Alternatively, using a variation on the approach used in your own answer:

CREATE INDEX [IX dbo.TestNegativeFilter StatusID not 9-14]
ON dbo.TestNegativeFilter (StatusID)
WHERE StatusID <> 9
AND StatusID <> 10
AND StatusID <> 11
AND StatusID <> 12
AND StatusID <> 13
AND StatusID <> 14;

Despite the filter being written as conjunctions, it supports queries written in any of the following ways (the first being slightly more efficient):

• StatusID NOT IN (9, 10, 11, 12, 13, 14)
• StatusID < 9 OR StatusID > 14
• StatusID NOT BETWEEN 9 AND 14

Answer 2

no great, but seems to be working:

create index FIDX_tblbOrders_sdtmOrdCreated_INCL 
on dbo.tblBOrder(sdtmOrdCreated)
INCLUDE (sintMarketID,
         strCurrencyCode,
         sintOrderStatusID
         )
WHERE ((sintMarketId=1)
AND ( sintOrderStatusId IN (0,1,2,3,4,5,6,7,8,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)))

When I try to do it in a better way it does not like it: