PRB: ANSI_NULLS OFF Behavior in SQL Server 6.x and 7.0

ID: Q214601

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5, 7.0

SYMPTOMS

Queries executed with ANSI_NULLS OFF may return varying or unintuitive results depending on the situation and SQL Server version. This article explicitly defines the expected behavior for all types of comparisons with SQL Server 6.x and SQL Server 7.0 with ANSI_NULLS OFF.

WORKAROUND

Always use ANSI_NULLS ON and use the IS NULL and IS NOT NULL syntax when doing comparisons with NULL values. ANSI_NULLS OFF behavior is included in SQL Server 7.0 only for backwards compatibility.

Consider the following example:


create table #t1 (a int, b int)
go
insert #t1 values (1, NULL)
insert #t1 values (2, 2)
go

If you want to return all the rows where b <> 2, and also want to include rows where b is NULL in the result set, use the following query:


select * from #t1 where (b <> 2 or b IS NULL)

MORE INFORMATION

SQL Server 7.0 Books Online states the following in the SET ANSI_NULLS (TSQL) topic:

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.

The nuances of the ANSI_NULLS OFF behavior are not explicitly detailed in the above section. In almost all cases, SQL Server 7.0 behavior with ANSI_NULLS OFF is the same as SQL Server 6.x. The one instance where SQL Server 6.x and 7.0 behave differently is when evaluating <> with variable expressions that evaluate to NULL. For example, in SQL Server 6.x, the query below would not return rows where the price column is NULL (it returns 16 rows total). In SQL Server 7.0, it returns the same rows as 6.x, plus the rows where the price column is NULL (18 rows total).


use pubs
set ansi_nulls off
go
declare @i money
select @i = 1.00
select price from titles where price <> @i
go

The tables below define the expected behavior for SQL Server 6.x and 7.0 with comparisons using ANSI_NULLS OFF behavior. The column heading abbreviations are described in the following key:

Tables Key:

K_NULL	Null constant (the word NULL)
K_NONNULL	Nonnull constant (for example, 1)
V_NULL	Variable (or parameter) with a value of null
V_NONNULL	Variable (or parameter) with a nonnull value
E_NULL	Expression other than constant or variable, which evaluates to null
E_NONNULL	Expression other than constant or variable, which does not evaluate to null

SQL Server 7.0 with = comparison and ANSI_NULLS OFF

	E_NULL	E_NONNULL	V_NULL	V_NONNULL	K_NULL	K_NONNULL
E_NULL	FALSE	FALSE	TRUE	FALSE	TRUE	FALSE
E_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****
V_NULL	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE
V_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****
K_NULL	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE
K_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****

SQL Server 7.0 with <> comparison and ANSI_NULLS OFF

	E_NULL	E_NONNULL	V_NULL	V_NONNULL	K_NULL	K_NONNULL
E_NULL	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
E_NONNULL	FALSE	*****	TRUE	*****	TRUE	*****
V_NULL	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
V_NONNULL	TRUE	*****	TRUE	*****	TRUE	*****
K_NULL	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
K_NONNULL	FALSE	*****	TRUE	*****	TRUE	*****

SQL Server 6.x with = comparison and ANSI_NULLS OFF

	E_NULL	E_NONNULL	V_NULL	V_NONNULL	K_NULL	K_NONNULL
E_NULL	FALSE	FALSE	TRUE	FALSE	TRUE	FALSE
E_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****
V_NULL	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE
V_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****
K_NULL	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE
K_NONNULL	FALSE	*****	FALSE	*****	FALSE	*****

SQL Server 6.x with <> comparison and ANSI_NULLS OFF

	E_NULL	E_NONNULL	V_NULL	V_NONNULL	K_NULL	K_NONNULL
E_NULL	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
E_NONNULL	FALSE	*****	TRUE	*****	TRUE	*****
V_NULL	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
V_NONNULL	FALSE	*****	TRUE	*****	TRUE	*****
K_NULL	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
K_NONNULL	FALSE	*****	TRUE	*****	TRUE	*****

Additional query words: ODBC OLEDB OLE DB DBLIB ANSI_DEFAULTS resultset non-null

Keywords :
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbprb