为什么不使用IS NULL值的过滤索引？

Assume we have a table definition like this:

假设我们有一个像这样的表定义：

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )

And a filtered non-clustered index like this:

像这样过滤的非聚集索引：

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab 
    (GroupByColumn)
WHERE (WhereColumn IS NULL)

Why this index is not "covering" for this query:

为什么此索引不“覆盖”此查询：

SELECT 
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn

I'm getting this execution plan:

我得到了这个执行计划：

The KeyLookup is for the WhereColumn IS NULL predicated.

KeyLookup用于WhereColumn IS NULL谓词。

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

这是计划：https：//www.brentozar.com/pastetheplan/？id = SJcbLHxO7

1 个解决方案

#1

Why this index is not "covering" for this query:

为什么此索引不“覆盖”此查询：

No good reason. That is a covering index for that query.

没有充分的理由。这是该查询的覆盖索引。

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

请在此投票支持费用：https：//feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

并且作为解决方法包括筛选索引中的WhereColumn：

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn 
ON MyTab (GroupByColumn) include (WhereColumn)
WHERE (WhereColumn IS NULL)

#1