sql-server – 无法在计算列上创建筛选索引

在我之前的一个问题,Is it a good idea to disable lock escalation while adding new calculated columns to a table?,我正在创建一个计算列：

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;

计算列为PERSISTED,根据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.

但是当我尝试在我的列上创建索引时,我收到以下错误：

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.

如何在计算列上创建筛选索引？

要么

有替代解决方案吗？

解决方法

不幸的是,从SQL Server 2014开始,无法创建过滤器位于计算列上的过滤索引(无论是否持久化).

自2009年以来已开放Connect Item,所以请继续投票.也许微软有一天会解决这个问题.

Aaron Bertrand有一篇文章介绍了Filtered Indexes的其他一些问题.