THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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



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


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:

Published Saturday, December 5, 2009 4:34 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS




I agree that filtered indexes should be available for persisted computed columns. As you probably have seen SQL BOL for CREATE INDEX states the following under WHERE <filter_predicate>: "The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column." That doesn't really help much... I wonder what the underlying reason is for this restriction.

Likely you have already thought about the following but I thought I would throw it out there: You may wish to fall back to an INPUT/UPDATE trigger that populates the MixedValueColumnAsInt column. You could define the MixedValueColumnAsInt as SPARSE. You should then be able to create the filtered index on this new column.

December 6, 2009 10:33 PM

RichB said:

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!

December 9, 2009 6:13 AM

Alex Meyer-Gleaves said:

+1 from me Greg.

December 13, 2009 7:21 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement