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

Question

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.

Answer 1

this collection of posts seems to be suggesting that with the combination of a view (with predicate where row_version_number > 370877585 ) and index on that view, perhaps the computed column could be the target of a "filtered search". i'm not sure how much (if any) additional storage that would take up but presumably the first index i mentioned wouldn't need to be create then so its probably a wash? Not sure if the view itself would require additional storage though.

https://dba.stackexchange.com/questions/116347/unable-to-create-a-filtered-index-on-a-computed-column

not sure what schema binding is or if that would interfere.

Answer 2

would hate to do it but that thought about building the index on the timestamp would look something like this...

CREATE NONCLUSTERED INDEX revise
ON [dbo].[revised1] (row_version_stamp)
WHERE row_version_stamp >= cast(370877585 as binary(8)) ;

we keep the last bigint high keys in a lookup table by table name.

Answer 3

i was surprised to see that using this predicate syntax instead in the filtered index definition gets an error...

where cast(row_version_stamp as bigint) >= 370877585

Answer 4

Hi，@db042190
Welcome to Microsoft T-SQL Q&A Forum!

In 2012, many people encountered this problem. SQL SERVER does not allow to create filter indexes on computed columns. You may try to use indexed views to solve this problem. Here are the related link which you can refer to .

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

thx bert. we arent on 2012. we are on 2019 now and still get that error when trying to build a filtered index on a computed column. maybe i'm misunderstanding what you are saying.