In SQL Server, What Does "Set Ansi_Nulls On" Mean

In SQL Server, what does SET ANSI_NULLS ON mean?

It means that no rows will be returned if @region is NULL, when used in your first example, even if there are rows in the table where Region is NULL.

When ANSI_NULLS is on (which you should always set on anyway, since the option to not have it on is going to be removed in the future), any comparison operation where (at least) one of the operands is NULL produces the third logic value - UNKNOWN (as opposed to TRUE and FALSE).

UNKNOWN values propagate through any combining boolean operators if they're not already decided (e.g. AND with a FALSE operand or OR with a TRUE operand) or negations (NOT).

The WHERE clause is used to filter the result set produced by the FROM clause, such that the overall value of the WHERE clause must be TRUE for the row to not be filtered out. So, if an UNKNOWN is produced by any comparison, it will cause the row to be filtered out.

---

@user1227804's answer includes this quote:

If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

from SET ANSI_NULLS^*

However, I'm not sure what point it's trying to make, since if two NULL columns are compared (e.g. in a JOIN), the comparison still fails:

create table #T1 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T1(ID,Val1) select 1,null

create table #T2 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T2(ID,Val1) select 1,null

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and t1.Val1 = t2.Val1

The above query returns 0 rows, whereas:

SELECT * FROM #T1 t1 INNER JOIN #T2 t2 
   ON t1.ID = t2.ID 
  AND (   t1.Val1 = t2.Val1 
       OR t1.Val1 IS NULL 
      AND t2.Val1 IS NULL   )

Returns one row. So even when both operands are columns, NULL does not equal NULL. And the documentation for = doesn't have anything to say about the operands:

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

If ANSI_NULLS is set to ON, the result is NULL¹, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

Comparing NULL to a non-NULL value always results in FALSE².

However, both ¹ and ² are incorrect - the result of both comparisons is UNKNOWN.

---

^*The cryptic meaning of this text was finally discovered years later. What it actually means is that, for those comparisons, the setting has no effect and it always acts as if the setting were ON. Would have been clearer if it had stated that SET ANSI_NULLS OFF was the setting that had no effect.

How to Deal with SET ANSI_NULLS ON or OFF?

SET ANSI_NULLS is ony defined at stored proc create time and cannot be set at run time.

From CREATE PROC

Using SET Options

The Database Engine saves the settings
of both SET QUOTED_IDENTIFIER and SET
ANSI_NULLS when a Transact-SQL stored
procedure is created or modified.
These original settings are used when
the stored procedure is executed.
Therefore, any client session settings
for SET QUOTED_IDENTIFIER and SET
ANSI_NULLS are ignored when the stored
procedure is running. Other SET
options, such as SET ARITHABORT, SET
ANSI_WARNINGS, or SET ANSI_PADDINGS
are not saved when a stored procedure
is created or modified. If the logic
of the stored procedure depends on a
particular setting, include a SET
statement at the start of the
procedure to guarantee the appropriate
setting. When a SET statement is
executed from a stored procedure, the
setting remains in effect only until
the stored procedure has finished
running. The setting is then restored
to the value the stored procedure had
when it was called. This enables
individual clients to set the options
they want without affecting the logic
of the stored procedure.

The same applies to SET QUOTED_IDENTIFIER

In this case, use IF ELSE because SET ANSI_NULLS will be ON in the future.

Or Peter Lang's suggestion.

To be honest, expecting SubDomainId = @SubDomainId to work when @SubDomainId is NULL is not really correct usage of NULL...

SQL SERVER and SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON

Both are set at connection level, not database. If you don't specify them in your script, they will use the default connection settings. You can see these by right clicking your server in SSMS and selecting Properies. Go to the Connections Pane and there is a check box for ANSI NULL default On/Off. If neither are selected, the "default default" is ON.

You can also get the settings by using @@OPTIONS:

SELECT CASE @@OPTIONS & 1024 WHEN 0 THEN 'OFF' ELSE 'ON' END AS SET_ANSI_NULLS_ON,
       CASE @@OPTIONS & 2048 WHEN 0 THEN 'OFF' ELSE 'ON' END AS SET_ANSI_NULLS_OFF;

A full list of the bitwise values can be found here: Configure the user options Server Configuration Option

You can turn the ANSI NULLs on by default for connections by using:

EXEC sys.sp_configure N'user options', N'1024';
GO
RECONFIGURE;

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

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.

ANSI_NULLS flag in SQL Server?

1. select databasepropertyex('MyDatabaseName', 'IsAnsiNullsEnabled') will tell you the database default. Hitesh's answer will tell you the value for the current session.
2. The database has a default settting, and each session can override the database default.
3. The session value. However, the MSDN documentation says For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values. So while it may work, it's certainly against best practices.

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.

---

Related Topics