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

db042190 1,516 Reputation points
2022-03-24T12:14:58.88+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. db042190 1,516 Reputation points
    2022-03-25T18:24:04.42+00:00

    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.

    1 person found this answer helpful.

  2. db042190 1,516 Reputation points
    2022-03-24T12:21:40.593+00:00

    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.


  3. db042190 1,516 Reputation points
    2022-03-24T13:51:13.96+00:00

    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


  4. Bert Zhou-msft 3,421 Reputation points
    2022-03-25T06:21:47.077+00:00

    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.

    0 comments No comments

  5. db042190 1,516 Reputation points
    2022-03-25T17:59:27.967+00:00

    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.

    0 comments No comments