SQL Server - SET ANSI_NULLS ON/OFF - Features, Examples and Equivalents

ANSI_NULLS option defines whether =, <> and != operators can be used to compare with NULL in addition to IS NULL and IS NOT NULL predicates.

Syntax SET ANSI_NULLS ON | OFF
Values ON allows only IS [NOT] NULL OFF enables =, <> and != with NULL
Default ON since SQL Server 2008 OFF in earlier versions
Note SET ANSI_DEFAULTS ON/OFF automatically sets ANSI_NULLS ON/OFF
Get Current Execute SELECT SESSIONPROPERTY('ANSI_NULLS') or DBCC USEROPTIONS

Versions: Microsoft SQL Server 2008 R2

Related Settings in SQL Server

Related settings for ANSI_NULLS in SQL Server:

ANSI_DEFAULTS Sets a group of ANSI/ISO SQL related settings
ANSI_PADDING Specifies whether trailing blanks are stored or trimmed

SET ANSI_NULLS Details

When ANSI_NULLS is ON, the conditions exp = NULL, exp <> NULL and exp != NULL are never true:

   CREATE TABLE t_nulls1 (c1 CHAR, c2 INT);
 
   INSERT INTO t_nulls1 VALUES ('A', 1);
   INSERT INTO t_nulls1 VALUES ('B', NULL);
 
   SELECT c1 FROM t_nulls1 WHERE c2 = NULL;
   -- 0 row(s) selected
 
   SELECT c1 FROM t_nulls1 WHERE c2 <> NULL;
   -- 0 row(s) selected
 
   -- Only IS NULL and IS NOT NULL can be used
   SELECT c1 FROM t_nulls1 WHERE c2 IS NOT NULL;
   -- Result: A

If you execute SET ANSI_NULLS OFF or SET ANSI_DEFAULTS OFF, the conditions exp = NULL, exp <> NULL and exp != NULL are true if exp is NULL:

  SET ANSI_NULLS OFF
 
   SELECT c1 FROM t_nulls1 WHERE c2 <> NULL;
   -- Result: A
 
   SELECT c1 FROM t_nulls1 WHERE c2 = NULL;
   -- Result: B
 
   -- IS NULL and IS NOT NULL still can be used when ANSI_NULLS is OFF
   SELECT c1 FROM t_nulls1 WHERE c2 IS NOT NULL;
   -- Result: A

ANSI_NULLS in Other Databases

Comparison with NULL in other databases:

Oracle:

IS NULL and IS NOT NULL only = NULL, <> NULL and != NULL are never true No option to change this behavior

Resources