SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.
Syntax
SET ANSI_NULLS {ON | OFF}
Remarks
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. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.
Note Whether Microsoft® SQL Server™ interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting of sp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If not specified, the setting of the ANSI nulls option of the current database applies. For more information about the ANSI nulls database option, see sp_dboption and Setting Database Options.
For a script to work as intended, regardless of the ANSI nulls database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values.
For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.
SET ANSI_NULLS should be set to ON for executing distributed queries.
SET ANSI_NULLS also must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error listing all SET options violating the required values. In addition, when executing a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select as though there were no such indexes on the tables or views.
Note ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL also must be set to ON, while NUMERIC_ROUNDABORT must be set to OFF.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. SET ANSI_NULLS defaults to OFF for connections from DB-Library applications.
When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.
The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.
Permissions
SET ANSI_NULLS permissions default to all users.
Examples
This example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and nonnull values in a table. This example also demonstrates that IS NULL is not affected by the SET ANSI_NULLS setting.
-- Create table t1 and insert values.
CREATE TABLE t1 (a int null)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO
-- Print message and perform SELECT statements.
PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- Drop table t1.
DROP TABLE t1
GO