Should be possible to create a filtered index on a deterministic persisted computed 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 computed column like so:
CREATE TABLE dbo.LousyTable
( ColumnWithMixedValues varchar(20),
CASE WHEN ISNUMERIC(ColumnWithMixedValues) = 1
THEN CAST(ColumnWithMixedValues AS int)
After indexing the computed 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)
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.
Upvotes: 158<=-=Dec 4 2009 10:01PM=-=>
I think this goes further than computed columns, as a filtered index can’t be used for something as simple as WHERE num % 2 = 0 (with no computed columns in sight). I really want to see filtered indexes become more useful.<=-=Dec 7 2009 10:29AM=-=>
Thanks for your feedback. I agree we should allow filtered indexes on persisted computed columns, and should support more complex filter expressions, at some point in the future. We restricted the functionality for the first version but we’ll consider this for a future release. The documentation should be more precise about the restrictions on predicate expressions. I’ll follow up on that.
I was AMAZED when I could not create a filtered index on computed column.
But I as APPALLED and DUMBFOUNDED when I could not create a filtered indexes on persisted computed column?
So a future release should address computed columns. But a patch should address persisted computed columns for SQL 2008.
Otherwise, it’s yet another “half-baked” feature.<=-=Jan 4 2010 1:02PM=-=>
Like SQL Ranger, I can’t believe this doesn’t work. Especially when you can’t compare two columns from the table in the predicate. These limitations take filtered indexes from interesting and very useful to pretty much a throwaway feature. Quite sad.<=-=Jan 4 2010 1:15PM=-=>
This functionality should definitely be in SQL11, if not in 2008 R2. There are almost no new Engine or T-SQL enhancemnets in 2008 R2.<=-=Jan 5 2010 7:00AM=-=>
Even more annoying is the vague error message when you do something like this:
CREATE TABLE dbo.splunge
CREATE INDEX c
ON dbo.splunge(foo, bar)
WHERE foo > bar;
Msg 10735, Level 15, State 1, Line 3
Incorrect WHERE clause for filtered index ‘c’ on table ‘dbo.splunge’.
Sooooooo, Denali CTP1 does not have this fixed. Any status, MS?<=-=Jan 23 2014 4:38AM=-=>
Worth noting that SSDT thinks that this is supported – a database project containing an index filtered on a deterministic persisted computed column will build with no errors, then fail to deploy.<=-=Feb 5 2014 2:36AM=-=>
It has now been over 4 years(!!!!) since this was raised. Has there been any progress with this?<=-=Oct 22 2015 2:20AM=-=>
Why hasnt this been fixed yet, OR why has there been no comment from Microsoft on this ???
There is ofcause an workaround, see more here: http://dba.stackexchange.com/questions/116347/unable-to-create-a-filtered-index-on-a-computed-column<=-=Nov 9 2015 2:24AM=-=>
It is a shame. I am not able to use complex logic in the filter index definition (I mean an OR clause). So, I have created a computed column to fix this but the filter index creation is not working as well.<=-=Oct 26 2017 8:10AM=-=>
Can we please get another update on the progress of this suggestion (ideally before we hit the approaching 8 year anniversary!!!)?
Daniel Smith commented
Any progress with this? There's the potential for some nice performance wins with this, if it can be enabled.