Microsoft KB Archive/214601

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

Article ID: 214601

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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)



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:

SQL Server 6.x with <> comparison and ANSI_NULLS OFF
Additional query words: ODBC OLEDB OLE DB DBLIB ANSI_DEFAULTS resultset non-null

Keywords: kbprb KB214601

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.