Odd that you can't create a filtered index on a deterministic persisted calculated column

On a client site the other day, I came across a situation (unfortunately too common) where a column in a table was being used for two purposes. It could either hold an integer value or a string. Only about 100 rows out of many millions had the integer value. Some of the client code needed to calculate the maximum value when it was an integer. First step I tried was to add a persisted calculated column like so:

CREATE TABLE dbo.LousyTable

( ColumnWithMixedValues varchar(20),

  SomeOtherColumn varchar(10),

  MixedValueColumnAsInt AS

    CASE WHEN ISNUMERIC(ColumnWithMixedValues) = 1

         THEN CAST(ColumnWithMixedValues AS int)

         ELSE NULL

    END PERSISTED

);

After indexing the calculated column, all was good. But I then thought I should create a filtered index instead:

CREATE INDEX IndexAttempt1 ON dbo.LousyTable (MixedValueColumnAsInt)

WHERE MixedValueColumnAsInt IS NOT NULL;

but this fails with:

Msg 10609, Level 16, State 1, Line 1

Filtered index 'IX_LousyTable' cannot be created on table 'dbo.LousyTable' because the column 'MixedValueColumnAsInt' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

I was discussing this with fellow MVP Rob Farley and we tried some other options such as:

CREATE INDEX IndexAttempt2 ON dbo.LousyTable (MixedValueColumnAsInt)

WHERE ISNUMERIC(ColumnWithMixedValues) = 1;

 

CREATE INDEX IndexAttempt3 ON dbo.LousyTable(MixedValueColumnAsInt)

WHERE CASE WHEN ISNUMERIC(ColumnWithMixedValues) = 1

           THEN CAST(ColumnWithMixedValues AS int)

           ELSE NULL

      END IS NOT NULL;

Regardless, there's no option to do this. I really think there should be. It's hard to imagine why it isn't permitted.

If you think so too, here's the connect item to vote on:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=518328

2 thoughts on “Odd that you can't create a filtered index on a deterministic persisted calculated column”

  1. I suspect that the problem lies in your use of case.
    I tried recently to create a filtered index to cover something similar the following query restriction:
    where field1 = 1 or field2 is not null or field3 is null or field4 !=5
    The filtered index works fine on AND statements, but wouldn't accept a coalesce() to match the above, nor would it accept the ORs in the filter clause.
    Ended up putting 4 seperate filtered indexes on the table and splitting the query into 4 union alled statements for a massive performance boost!

Leave a Reply

Your email address will not be published.