SQL Server筛选索引WHERE列=列(SQL Server Filtered Index WHERE Column = Column)

SQL Server筛选索引WHERE列=列(SQL Server Filtered Index WHERE Column = Column)

我希望尝试在SQL Server 2012的表上使用过滤索引，看看它是否会改善查询执行，但在尝试创建它时，我收到以下错误：

Msg 10735，Level 15，State 1，Line 3 表'dbo.SRReferralIn'上的筛选索引'IX_SRReferralIn_Filtered'的WHERE子句不正确。

以下是我正在使用的声明。 RowIdentifier和IDOrganisationVisibleTo是CLUSTERED PRIMARY KEY中的列

CREATE NONCLUSTERED INDEX IX_SRReferralIn_Filtered ON dbo.SRReferralIn(RowIdentifier, IDOrganisationVisibleTo) WHERE IDOrganisationVisibleTo = IDOrganisation;

是否不支持WHERE子句中的表达式？

I was hoping to try use a filtered index on a table in SQL Server 2012 to see if it would improve query execution though when trying to create it I am getting the following error:

Msg 10735, Level 15, State 1, Line 3 Incorrect WHERE clause for filtered index 'IX_SRReferralIn_Filtered' on table 'dbo.SRReferralIn'.

Below is the statement I am using. RowIdentifier and IDOrganisationVisibleTo are the columns in the CLUSTERED PRIMARY KEY

CREATE NONCLUSTERED INDEX IX_SRReferralIn_Filtered ON dbo.SRReferralIn(RowIdentifier, IDOrganisationVisibleTo) WHERE IDOrganisationVisibleTo = IDOrganisation;

Is the expression in the WHERE clause not supported?

最满意答案

不， 这不受支持 。

语法只允许与常量进行比较

<filter_predicate> ::= <conjunct> [ AND <conjunct> ] <conjunct> ::= <disjunct> | <comparison> <disjunct> ::=column_name IN (constant ,...n) <comparison> ::=column_name <comparison_op> constant<comparison_op> ::= { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

您可以使用此条件创建索引视图。

No this is not supported.

The grammar only allows comparisons with constants

<filter_predicate> ::= <conjunct> [ AND <conjunct> ] <conjunct> ::= <disjunct> | <comparison> <disjunct> ::=column_name IN (constant ,...n) <comparison> ::=column_name <comparison_op> constant<comparison_op> ::= { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

You could create an indexed view with this condition though.