I have a table 开发者_运维百科with a dateTime column on which I want to put a filtered index. This index will get rebuilt each week. Each time it's rebuilt, I want it to include rows two days old and newer, based on this column. Can I create such a filtered index? I've tried various approaches and I get syntax errors.
For example, the following Where clause on the index creation did not work:
WHERE (ReadTime > DateAdd(dd,-2,GetDate()))
You can't do this by referencing getdate()
directly. You would need dynamic SQL.
The CREATE INDEX
grammar only allows comparisons against a constant.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
... /*Irrelevant grammar removed*/
<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 | = | <> | != | > | >= | !> | < | <= | !< }
精彩评论