PRB: ANSI_NULLS OFF Behavior in SQL Server 6.x and 7.0 (214601)
The information in this article applies to:
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
- Microsoft SQL Server 7.0
This article was previously published under Q214601 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)
Modification Type: | Major | Last Reviewed: | 11/14/2003 |
---|
Keywords: | kbprb KB214601 |
---|
|