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

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

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 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	*****