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

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

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  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 14

• StatusID NOT BETWEEN 9 AND 14

StackExchange Database Administrators Q#116378, answer score: 12