T-SQL / 关闭ANSI_NULLS时意外的空处理

Question

我只是在SQL服务器中为空值处理而苦苦挣扎(在版本12.0.5000.0上测试(。基本上，我的目的是获取所有具有列值<>静态值的行(例如 999(。我不是在搜索诸如"使用 ISNULL 函数"之类的替代方案。查询是由第三方引擎生成的，我不打算编写解析器并更改语句。

-- All examples with ANSI_NULLS OFF
SET ANSI_NULLS OFF;
GO
--------------------------------------------------------------------------------------------------------
-- "Virtual" example / working as expected
--------------------------------------------------------------------------------------------------------
DECLARE 
@One INT = 1,
@Null INT = NULL
SELECT
IIF(@Null = NULL, 1, 0) '@Null = NULL' -- To test if ANSI_NULL is OFF
,IIF(@One <> NULL, 1, 0) '@One <> NULL' -- working with NULL variable
,IIF(1 <> NULL, 1, 0) '1 <> NULL'       -- working with NULL value
--------------------------------------------------------------------------------------------------------
-- MSDN Example / NOT working as expected
-- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql
--------------------------------------------------------------------------------------------------------
-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
-- SET ANSI_NULLS to OFF and test.  
DECLARE @varname int;  
SET @varname = 999;
SELECT a   
FROM t1   
WHERE a <> @varname;    -- working with NULL variable
SELECT a   
FROM t1   
WHERE a <> 999;         -- NOT working with NULL value
-- Drop table t1.  
DROP TABLE dbo.t1;  

谁能解释为什么"虚拟"示例的工作方式与 MSDN 示例不同？

Virtual example:
+--------------+--------------+-----------+
| @Null = NULL | @One <> NULL | 1 <> NULL |
+--------------+--------------+-----------+
|            1 |            1 |         1 |
+--------------+--------------+-----------+
MSDN example:
-- SELECT 1
+------+
|  a   |
+------+
| NULL |
| 0    |
| 1    |
+------+
-- SELECT 2
+------+
|  a   |
+------+
| 0    |
| 1    |
+------+

Answer 1

看起来查询优化器选择了不同的比较运算符：

DECLARE @varname int;  
SET @varname = 999;
SELECT a   
FROM t1   
WHERE a <> @varname;

XML 执行计划：

<Predicate>
<ScalarOperator ScalarString="[fiddle_84f7799901e54a779e8bff464a2d01f3].[dbo].[t1].[a] &lt;&gt; [@varname]">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[fiddle_84f7799901e54a779e8bff464a2d01f3]" Schema="[dbo]" Table="[t1]" Column="a"></ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@varname"></ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate> 

Compare CompareOp="IS NOT">

---

秒查询，具有硬编码值：

SELECT a   
FROM t1   
WHERE a <> 999; 
-- same as
DECLARE @varname int = 999;
SELECT a   
FROM t1   
WHERE a <> (SELECT @varname);

XML 执行计划：

<Predicate>
<ScalarOperator ScalarString="[fiddle_ac5121a789da473382366733b51ef441].[dbo].[t1].[a]&lt;&gt;(999)">
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[fiddle_ac5121a789da473382366733b51ef441]" Schema="[dbo]" Table="[t1]" Column="a"></ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(999)"></Const>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>

比较

比较操作="NE">

DBFiddle

编辑：

设置ANSI_NULLS

SET ANSI_NULLS ON 仅在比较的操作数之一为 NULL 或文本 NULL 时影响比较。如果比较的双方都是列或复合表达式，则设置不会影响比较。