T-SQL / неожиданная обработка NULL, когда ANSI_NULLS выключен

Question

Я просто борюсь с обработкой значения NULL в SQL Server (проверено на версии 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://docs.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 ="НЕТ"

---

Второй запрос с жестко заданным значением:

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>

Compare CompareOp ="NE"

DBFiddle

РЕДАКТИРОВАТЬ:

SET ANSI_NULLS

SET ANSI_NULLS ON влияет на сравнение, только если один из операндов сравнения является либо переменной, равной NULL, либо литералом NULL. Если обе стороны сравнения являются столбцами или составными выражениями, настройка не влияет на сравнение.