Sql-server – Unable to create a Filtered Index on a Computed Column

computed-columnfiltered-indexindexsql serversql server 2014

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST
(
    ISNULL(
        CASE WHEN sintMarketID = 2 
            AND strType = 'CARD'
            AND strTier1 LIKE 'GG%' 
        THEN 1 
        ELSE 0 
        END
    , 0) 
    AS BIT
) PERSISTED;

The calculated column is PERSISTED, and according to computed_column_definition (Transact-SQL):

PERSISTED

Specifies that the Database Engine will physically store the computed
values in the table, and update the values when any other columns on
which the computed column depends are updated. Marking a computed
column as PERSISTED allows an index to be created on a computed column
that is deterministic, but not precise. For more information, see
Indexes on Computed Columns. Any computed columns used as partitioning
columns of a partitioned table must be explicitly marked PERSISTED.
computed_column_expression must be deterministic when PERSISTED is
specified.

But when I try to create an index on my column I get the following error:

CREATE INDEX FIX_tblBGiftVoucherItem_incl
ON dbo.tblBGiftVoucherItem (strItemNo) 
INCLUDE (strTier3)
WHERE isUsGift = 1;

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

How can I create a filtered index on a computed column?

or

Is there an alternative solution?

Best Answer

Unfortunately as of SQL Server 2014, there is no ability to create a Filtered Index where the Filter is on a Computed Column (regardless of whether or not it is persisted).

There has been a Connect Item open since 2009, so please go ahead and vote for it. Maybe Microsoft will fix this one day.

Aaron Bertrand has an article that covers a number of other issues with Filtered Indexes.

Related Question