Functions of set ansi_nulls on

Specifies the SQL-92 behavior of these operators when null values are compared using equal to (=) and not equal to (<>.

Syntax

Set ansi_nulls {on | off}

Note

The SQL-92 standard requires a value of equal to (=) or not equal to (<>) for a null value. When set ansi_nulls is onColumn_nameThere is a null value in, use whereColumn_nameThe SELECT statement with = NULL still returns zero rows. Even ifColumn_nameThere is a non-null value in, use whereColumn_name<> Null select statements still return zero rows.

When set ansi_nulls is off, equal to (=) and not equal to (<>) Comparison operators do not comply with SQL-92 standards. Use whereColumn_nameReturns a SELECT statement with = NULL.Column_nameContains null rows. Use whereColumn_name<> A null SELECT statement returns rows with non-null values in the column. In addition, use the SELECT statement where column_name <> xyz_value to return all rows with non-XYZ values and non-null values.

DescriptionMicrosoft SQL Server interprets a null string as a single space or a real Null String, depending onSp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL Server interprets the Null String as a single space. If the compatibility level is 70, SQL Server interprets the Null String as a null string. For more information, see Sp_dbcmptlevel.

When set ansi_nulls is on, all Null Value Comparison values are unknown. When set ansi_nulls is off, if the data value is null, the comparison of all data null values is true. If this parameter is not specifiedANSI nullsOptions. RelatedANSI nullsFor more information about Database options, see sp_dboption and set database options.

To make the script run as expectedANSI nullsWhat are the database options or set ansi_nulls settings? Use is null and is not null in comparisons that may contain null values.

For stored procedures, SQL server uses the set ansi_nulls value when the stored procedure was initially created. Set ansi_nulls is restored to its original value and takes effect no matter when the stored procedure is executed subsequently. When set ansi_nulls is invoked during the stored procedure, its settings are not changed.

Set ansi_nulls to on when executing a distributed query.

When you create or operate an index on a calculated column or index view, set ansi_nulls must also be on. If set ansi_nulls is off, the CREATE, update, insert, and delete statements on the table with an index on the calculation column or index view will fail. SQL Server Returns An error listing all set options that violate the required values. In addition, if set ansi_nulls is off when the SELECT statement is executed, SQL Server ignores the index value on the calculated column or view and parses the selection, it is like no such index on a table or view.

DescriptionAnsi_nulls is one of the seven set options that must be set to the desired value when processing indexes on the computing column or index view. You must also set the options ansi_padding, ansi_warnings, arithabort, quoted_identifier, and concat_null_yields_null to on, And numeric_roundabort to off.

The SQL Server ODBC driver and the Microsoft ole db provider for SQL Server automatically set ansi_nulls to on when connecting. This setting can be configured in the ODBC data source, ODBC connection feature, or ole db connection attribute (they are set in the application before they are connected to SQL Server. Set ansi_nulls is off by default for connections from DB-Library applications.

When set ansi_defaults is on, set ansi_nulls is enabled.

Set ansi_nulls is set during execution or running, rather than during analysis.

Permission

Set ansi_nulls Permissions are granted to all users by default.

Example

The following example uses the EQUAL (=) and not equal to (<>) Comparison operators to compare null values and non-null values in the table. The following example also shows that is null is not affected by the set ansi_nulls settings.

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

See

= (Equal)

If... else

<> (Not equal)

Set

Set ansi_defaults

Where

While