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

On a client site some years back, 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 TRY_CAST(ColumnWithMixedValues AS int) 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.

A while back, 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 TRY_CAST(ColumnWithMixedValues AS int) 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.

2026-04-24