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