SET ANSI_NULLS (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with NULL values in SQL Server.

• SET ANSI_NULLS ON - Evaluates both {expression} = NULL and {expression} <> NULL as False if the value of {expression} is NULL. This behavior is ANSI-compliant.
• SET ANSI_NULLS OFF - Evaluates {expression} = NULL as True and {expression} <> NULL as False if the value of {expression} is NULL. This behavior is not recommended, because the NULL values should not be compared using = and <> operators.

Transact-SQL syntax conventions

Syntax

Syntax for SQL Server, serverless SQL pool in Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Syntax for Azure Synapse Analytics and Analytics Platform System (PDW)

SET ANSI_NULLS ON

Remarks

When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are NULL values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-NULL values in column_name.

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have non-NULL values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

When 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 SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. For more information about the ANSI_NULLS database option, see ALTER DATABASE (Transact-SQL).

The following table shows how the setting of ANSI_NULLS affects the results of Boolean expressions using null and non-null values.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

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 might contain null values.

ANSI_NULLS should be set to ON for executing distributed queries.

ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views fail. SQL Server returns an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server ignores the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

The SQL Server Native Client ODBC driver and SQL Server Native Client 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 an instance of SQL Server. The default for SET ANSI_NULLS is OFF.

When ANSI_DEFAULTS is ON, ANSI_NULLS is enabled.

The setting of ANSI_NULLS is defined at execute or run time and not at parse time.

To view the current setting for this setting, run the following query:

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;   

Permissions

Requires membership in the public role.

Examples

The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL or 0 and the null value in a variable.

SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
    IIF(@var = NULL,  'True', 'False') as EqualNull,
    IIF(@var <> NULL, 'True', 'False') as DifferentNull,
    IIF(@var = 0,     'True', 'False') as EqualZero,
    IIF(@var <> 0,    'True', 'False') as DifferentZero

The results are show in the following table.

With SET ANSI_NULLS ON all expressions would be evaluated as 'False' because NULL cannot be compared with NULL or 0 using these operators.

The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and non-null values in a table. The example also shows the SET ANSI_NULLS setting does not affect IS NULL.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO 

Now set ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  

Now set ANSI_NULLS to OFF and test.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;  

Related content

• SET Statements (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Equals) (Transact-SQL)
• IF...ELSE (Transact-SQL)
• <> (Not Equal To) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• WHERE (Transact-SQL)
• WHILE (Transact-SQL)