我希望尝试在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.
更多推荐
发布评论