大网数据广告活动
数据库与应用安全

Filtered index with Column IS NULL isn't used

· 52CE 我爱测

This article provides resolution for the problem that occurs when you create the filtered index together with the Column IS NULL predicate expression in Microsoft SQL Server.

This article helps you resolve the problem that occurs when you create the index together with the Column IS NULL predicate expression in SQL Server.

Original product version:   SQL Server
Original KB number:   3051225

Symptoms

Consider the following scenario:

  • You create a filtered index together with the Column IS NULL predicate expression in SQL Server.
  • The Column field isn't included in the index structure. (That is, the Column field isn't a key or included column in the filtered index definition.)

For example, you create the following query:

CREATE UNIQUE CLUSTERED INDEX i_action_rn ON dbo.filter_test (rn)
CREATE NONCLUSTERED INDEX i_action_filt_action_date_type ON dbo.filter_test (action_type)
WHERE action_date IS NULL

Note: This query doesn't use the following filtered index:

SELECT count(*) FROM dbo.filter_test WHERE action_date IS NULL AND action_type=1

In this scenario, the filtered index isn't used. Instead, the clustered index is used.

Resolution

To resolve this issue, include the column that is tested as NULL in the returned columns. Or, add this column as include columns in the index.

CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type ON dbo.filter_test (action_type) include (action_date) WHERE action_date IS NULL

More information

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/14/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。