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.

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

要么

有替代解决方案吗？

解决方法

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

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

（编辑：宿迁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!