sql-server - 过滤索引寻找 `is NULL` 条件并扫描相反的条件

Question

假设有一个表，例如:

create table #data (ID int identity(1, 1) not NULL, Value int)

将一些数据放入其中:

insert into #data (Value)
select top (1000000) case when (row_number() over (order by @@spid)) % 5 in (0, 1) then 1 else NULL end
from sys.all_columns c1, sys.all_columns c2

还有两个索引:

create index #ix_data_n on #data (Value) include (ID) where Value is NULL
create index #ix_data_nn on #data (Value) include (ID) where Value is not NULL

数据查询如下:

select ID from #data where Value is NULL

或

select ID from #data where Value is not NULL

如果我检查查询计划，我会发现在第一种情况下执行索引查找，在第二种情况下执行索引扫描。为什么在第一种情况下进行查找，在第二种情况下进行扫描？

评论后添加:

如果我创建普通覆盖索引而不是两个过滤覆盖:

create index #ix_data on #data (Value) include (ID)

查询计划显示对 is NULL 和 is not NULL 条件的索引查找，忽略列中 NULL 值的百分比(NULL 的 0% 或 10% 或90% 或 100%，没关系)。当有两个过滤索引时，查询计划始终显示索引查找为is NULL，并且可以是索引扫描或表扫描(取决于 NULL 的百分比)，但绝不是索引查找。所以，看起来本质上的区别在于条件“不为空”的处理方式。

这可能意味着，如果索引仅用于“is not NULL”检查，那么普通索引或过滤索引应该表现更好并且是首选，不是吗？哪一个？

SqlServer 2008、2008r2 和 2012

Answer 1

您所看到的查询计划中的“Seek”与“Scan”是转移注意力的内容。

在这两种情况下，查询都是通过从头到尾扫描适当的非聚集索引并返回每一行来回答的。

通过检查 XML 查询计划，您可以看到索引 Seek 谓词是“#data.Value = Scalar Operator (Null)”，这是没有意义的，因为每一行都满足该条件。