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

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