我应该如何针对可以为 null 的 SQL 变量测试字段？

Question

我有以下 SQL：

CREATE TABLE tbFoo(
    a varchar(50) NULL,
) 


CREATE NONCLUSTERED INDEX IX_tbFoo_a ON tbFoo
(
    a ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

insert into tbFoo select null
insert into tbFoo select 'test'

以下两个查询工作正常，并按预期使用我的索引：

select * from tbFoo where a='test'
select * from tbFoo where a is null

现在，假设我想将比较值存储在变量中，如下所示：

declare @a varchar(50)
select @a = NULL

以下查询将不会返回预期结果，如果@a 为空，因为我应该使用“is”运算符而不是“=”

select * from tbFoo where a=@a 

以下内容将起作用，但如果 @a 为空，则会进行表扫描（因为“测试”行强制评估第二个括号）

select * from tbFoo where (a is null and @a is null) or (a=@a)

最终，我想出了这个解决方案，它运行良好并使用我的索引：

select * from tbFoo where (a is null and @a is null) or (@a is not null and a=@a)

我对情况的分析是否正确？

有没有更好的方法来处理这种情况？

Answer 1

最终，我想出了这个解决方案，它工作正常并使用我的索引：

在 SQL Server 2008 中，您可以基于排除 NULL 的谓词定义过滤索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_tbFoo_a 
ON tbFoo (a)
WHERE a IS NOT NULL;

Answer 2

没有什么东西“等于”NULL……这就是 NULL 的要点。

您的解决方案会正常工作。 我对查询优化器如何处理较短版本感到惊讶。 我认为在使用表扫描测试相等性之前测试 a 是否为 NULL 是理所当然的。

Answer 3

另一种可能性是将 ansi nulls 设置为 off

set ansi_nulls off

declare @a varchar(50)
select @a = NULL

select * from tbFoo where a=@a

set ansi_nulls on

请记住，您正在脱离此处的默认行为

Answer 4

也许您的数据库引擎会自动优化您所获得的内容，但在我看来，以下内容会更有效：

if @a IS NULL
    select * from tbFoo where a is null
else
    select * from tbFoo where a = @a

我对此的推理是您只需执行一次 if @a IS NULL 条件，而不是检查数据库中的每一行。 不过，高质量的数据库引擎应该能够将您的代码转换为与此相同类型的数据计划。

Answer 5

这就是我所做的。 它非常灵活。
我假设 @a 是存储过程的参数。 'somethingweird' 可能是您在记录集中永远不会看到的东西 '~~~' 或其他什么。

set @a = isnull(@a,'somethingweird')
select * from tbFoo where isnull(a,'somethingweird')=@a

Answer 6

我家里没有实例可以玩，但我可以看到表扫描变得非常烦人。 一种可能的替代方案是使用 UNION 代替 OR 运算符...

select * from tbFoo where (a is null and @a is null)
UNION ALL
select * from tbFoo where (a=@a and @a is not null)

（我不确定“@a 不为空”会对性能产生什么影响，但我的直觉是包含它。这是一个常量表达式应该让优化器知道整个条件何时总是失败。我的技术总是玩并看看什么最有效。）

我发现这个 UNION 技巧有两个属性：
- 它可以通过简化查询来显着提高性能
- 它会通过多个连接使代码膨胀并导致严重的维护麻烦

但是，生活只是一种平衡行为:)

Answer 7

只是 ISNULL 双方都像这样......

DECLARE @random VARCHAR(50)
SELECT  @random = 'text that never appears in your table'

SELECT * FROM @tbFoo WHERE ISNULL(a, @random) = ISNULL(@a, @random)

Answer 8

你的分析是正确的——这就是为什么三值逻辑让生活变得困难的原因。

@StriplingWarrior 的建议很好； 它通过根据变量是否为空执行不同的 SQL 来解决这个问题。 如果这是不可能的，则需要重复使用主机变量的冗长解决方案。