创建可空bit列的非聚集索引时语法错误排查

问题场景 #

尝试创建如下非聚集过滤索引：

Create nonclustered index ixtable on table (isdeleted)
  where isdeleted is null
  or isdeleted = 0

其中isdeleted是带默认值0的可空bit列，执行后报错：

incorrect syntax near null and near OR.

需通过该索引优化左连接3次的查询，筛选isdeleted为NULL或0的记录，但移除isdeleted is null虽能执行却无法满足需求，移除默认约束也无效。

语法错误原因 #

SQL Server的过滤索引（Filtered Index）语法明确限制：WHERE子句不允许使用OR运算符，同时无法直接将IS NULL与其他条件通过OR组合，这是导致报错的核心原因。

正确解决方案 #

由于isdeleted是可空bit列，有效值仅为0、1、NULL，可通过ISNULL函数将NULL转换为0，用单一条件替代OR逻辑，同时覆盖目标行：

CREATE NONCLUSTERED INDEX ixtable ON [table] (isdeleted)
WHERE ISNULL(isdeleted, 0) = 0;

该条件会匹配所有isdeleted = 0或isdeleted IS NULL的行，完全符合需求，且符合过滤索引的语法规则。

补充说明 #

• 过滤索引的WHERE子句仅支持AND逻辑组合的条件，不能使用OR；允许使用ISNULL这类确定性函数，但不支持子查询、聚合函数等复杂表达式。
• 索引创建后，查询筛选目标行时，SQL Server可自动调用该过滤索引，无需修改原查询的WHERE子句。

内容的提问来源于stack exchange，提问作者Imani Lim