8

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.

12

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
1

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:

enter image description here

  • 1
    According MSND documentation: "Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view." msdn.microsoft.com/en-us/library/cc280372.aspx – Paweł Tajs Oct 1 '15 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.