would really love to save space and use a filtered index on a computed column

db042190 1,521

hi we run 2019 and have the 2 following columns defined on a number of tables...

[ROW_VERSION_STAMP] [timestamp] NOT NULL,
[ROW_VERSION_NUMBER] AS (CONVERT([bigint],[ROW_VERSION_STAMP])),

we'd like an index on the second but only starting at a certain value. ie a filtered index. we get an error (shown below) attempting to build such an index. is there a workaround? i'm even thinking about building it instead on the binary(8) timestamp.

Filtered index 'revise' cannot be created on table 'dbo.revised1' because the column 'ROW_VERSION_NUMBER' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

6 answers

db042190 1,521 Reputation points

2022-03-29T14:50:17.937+00:00

this gets confusing. at https://learn.microsoft.com/en-us/answers/questions/784633/generating-a-script-for-building-a-new-index-on-a.html#answer-791781 erland posted a sample where he was able to create a filtered index on a computed column. i posted a sample there where i got an error. so far the differences are that i have a timestamp and bigint. and i think he used ints. i wonder if he has one of those other legacy settings maybe on or off? and maybe i have the opposite.
Please sign in to rate this answer.
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2022-03-29T20:23:26.01+00:00

I think the difference is that you have the computed column in the filter itself. In my examples I only had the non-computed columns in the filter. I did not occur to me that the filter could be the issue.

db042190 1,521 Reputation points

2022-03-30T16:34:17+00:00

ok, a lot less confusing now. thx
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

would really love to save space and use a filtered index on a computed column

6 answers

Your answer