sql - 我可以使用过滤索引来查询 "recently modified"行吗

Question

我正在使用 SQL Server 2008-R2，但我也对更通用的答案感兴趣......

我有一个包含数亿行的表，每行都有一个“DateModified”字段 (datetime2(7))

现在我经常使用类似的查询来轮询该表

select * from table where DateModified > @P1 

此处的参数始终是最近的(例如最近几分钟内)，并且可能只有少数记录与该值匹配。

我突然想到，我在整个表上维护一个大索引，而我永远不会为其中许多值使用索引......所以这听起来像是过滤索引的完美使用，我只索引我可能查询的行...

但是在这种情况下，过滤器会是什么样子呢？我唯一的想法是过滤

where DateModified > [yesterday]

其中[yesterday]是日期文字，但随后我必须定期重新定义过滤器，否则过滤器的优势会随着时间的推移而减弱。

一时兴起，我尝试了 ModifiedDate > DATEADD(d,-1,GETDATE()) 但这给出了一个不起眼的错误......不确定这怎么可能。

还有其他方法可以实现这一点吗？

最后，如果有办法做到这一点，我是否应该预期统计数据在我的情况下会出现严重错误，这会影响我的查询性能吗？

我对统计数据的担忧来自this article .

我正在尝试使用一些断开连接的数据将更改从一个系统传播到另一个系统...如果您想建议一种完全替代的方法来轮询“DateModified”，我很乐意考虑它。

Answer 1

不久前有类似的需求，发现过滤器中不允许使用函数。

您可以做的是编写索引脚本并安排它在非高峰(可能是夜间)时间在作业中运行。这也将解决统计问题，因为每次创建索引时都会重新创建它们。

以下是我们最终所做的示例:

    CREATE TABLE FilteredTest (
    TestDate datetime
    );

然后只需按计划运行此命令即可仅使用最新行进行创建:

DECLARE @sql varchar(8000) = '

IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = ''IX_FilteredTest_TestDate'')
    DROP INDEX IX_FilteredTest_TestDate ON FilteredTest;

CREATE NONCLUSTERED INDEX IX_FilteredTest_TestDate ON FilteredTest (
    TestDate
)
WHERE TestDate > ''' + CONVERT(varchar(25),DATEADD(d,-1,GETDATE()) ,121) + ''';';

EXEC (@sql);