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 &lt; 9) OR (sintOrderStatusId &gt; 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 &lt; 9);

CREATE INDEX IX_TestNagativeFilter_GreaterThan14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID &gt; 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 &lt;&gt; 9
AND StatusID &lt;&gt; 10
AND StatusID &lt;&gt; 11
AND StatusID &lt;&gt; 12
AND StatusID &lt;&gt; 13
AND StatusID &lt;&gt; 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 &lt; 9 OR StatusID &gt; 14
StatusID NOT BETWEEN 9 AND 14