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.

Versions: Microsoft SQL Server 2008 R2

Related settings for ANSI_NULLS in SQL Server:

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

Comparison with NULL in other databases:

Oracle:

• SQL Server Reference
• SET ANSI_NULLS - SQL Server 2008 R2 Books Online