Why Ansi_Nulls Does Not Work

SET ANSI_NULLS is not working

You are getting correct output. You just aren't getting the output you expected.

The documentation is quite clear that the ANSI_NULLS setting only applies to literal comparisons to NULL. From that page:

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

ansi_nulls: some ways to check it don't seem to work

Your queries are looking at two different things.

One is the database default (can be changed with ALTER DATABASE SET) - the other is what it is set to in the current session.

The database default is practically useless as all common ways of connecting to SQL Server set ANSI_NULLS on as described below

Connection-level settings that are set by using the SET statement
override the default database setting for ANSI_NULLS. ODBC and OLE DB
clients issue a connection-level SET statement setting ANSI_NULLS to
ON for the session, by default. The clients run the statement when you
connect to an instance of SQL Server. For more information, see SET
ANSI_NULLS.

T-SQL / Unexpected NULL handling when ANSI_NULLS is turned OFF

Looks like query optimizer chooses different comparison operator:

DECLARE @varname int;  
SET @varname = 999;

SELECT a   
FROM t1   
WHERE a <> @varname;

XML execution plan:

<Predicate>
    <ScalarOperator ScalarString="[fiddle_84f7799901e54a779e8bff464a2d01f3].[dbo].[t1].[a] <> [@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"

---

Second query with hardcoded value:

SELECT a   
FROM t1   
WHERE a <> 999; 

-- same as
DECLARE @varname int = 999;

SELECT a   
FROM t1   
WHERE a <> (SELECT @varname);

XML execution plan:

<Predicate>
    <ScalarOperator ScalarString="[fiddle_ac5121a789da473382366733b51ef441].[dbo].[t1].[a]<>(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

EDIT:

SET ANSI_NULLS

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

SQL Server 2008R2: SET ANSI_NULLS OFF does not affect merge matching with null values

SET ANSI_NULLS only affects the semantics of NULL comparisons in very limited cases. Specifically it

affects a comparison only if one of the operands of the comparison is
either a variable that is NULL or a literal NULL. If both sides of the
comparison are columns or compound expressions, the setting does not
affect the comparison. (source)

When you wrap the literal NULL in a derived table this condition is no longer met so it is not expected that this setting will do as you want.

Database options: why by default SET ANSI_NULLS OFF is OFF for new databases?

how it becomes ON in my scripts?

Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS.

By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to an instance of SQL Server.

I am confused why such important setting like ANSI_NULLS is OFF when I create a database?

I have created databases with both nulls off and on,but each time when i try to insert,Client drivers generated those set options as ON for me

Further below is what MSDN has to say about this..

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

closest,i can think of why this option is set to ON,may be due to compatabilty

Why does SQL Server require ansi_nulls to be on to create an index?

It has a base in being deterministic.

A calculated column or an indexed view for example must always return the same data no matter what the session settings are, and therefore they are forced to behave in a consistent manner by the engine.

The example here in the indexed view page shows one example of where different options can change the behaviour. Because a calculation could in some cases use an equality comparison between columns, consistent behaviour is needed here too

How does ANSI_NULLS work in TSQL?

The reason the last two queries fail is that SET ANSI_NULLS ON/OFF only applies when you are comparing against a variable or the NULL value. It does not apply when you are comparing column values. From the BOL:

SET ANSI_NULLS ON affects a comparison
only if one of the operands of the
comparison is either a variable that
is NULL or a literal NULL. If both
sides of the comparison are columns or
compound expressions, the setting does
not affect the comparison.

---

Related Topics