--- title: "SET ANSI_NULLS (Transact-SQL)" description: SET ANSI_NULLS configures the behavior of the ANSI NULLS setting for equals and not equals comparison operators. author: WilliamDAssafMSFT ms.author: wiassaf ms.date: 04/28/2025 ms.reviewer: jovanpop, randolphwest ms.service: sql ms.subservice: t-sql ms.topic: reference f1_keywords: - "SET_ANSI_NULLS_TSQL" - "ANSI_NULLS" - "SET ANSI_NULLS" - "ANSI_NULLS_TSQL" helpviewer_keywords: - "SET ANSI_NULLS statement" - "not equal to operator (<>)" - "ANSI_NULLS option" - "equals operator (=)" - "null values [SQL Server], comparison operators" - "comparison operators [SQL Server], null values" dev_langs: - "TSQL" monikerRange: ">=aps-pdw-2016||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||azuresqldb-current" --- # SET ANSI_NULLS (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)] Specifies ISO compliant behavior of the Equals (`=`) and Not Equal To (`<>`) comparison operators when they are used with `NULL` values in [!INCLUDE [ssnoversion](../../includes/ssnoversion-md.md)]. - `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. > [!NOTE] > `SET ANSI_NULLS OFF` and the `ANSI_NULLS OFF` database option are deprecated. Starting with [!INCLUDE [_ss2017](../../includes/sssql17-md.md)], ANSI_NULLS is always set to ON. Deprecated features shouldn't be used in new applications. For more information, see [Deprecated Database Engine features in SQL Server 2017](../../database-engine/deprecated-database-engine-features-in-sql-server-2017.md#transact-sql-1). :::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: [Transact-SQL syntax conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax #### Syntax for [!INCLUDE [ssnoversion-md.md](../../includes/ssnoversion-md.md)], [!INCLUDE [sssodfull-md.md](../../includes/sssodfull-md.md)], [!INCLUDE [fabric](../../includes/fabric.md)] ```syntaxsql SET ANSI_NULLS { ON | OFF } ``` #### Syntax for [!INCLUDE [ssazuresynapse-md.md](../../includes/ssazuresynapse-md.md)] and [!INCLUDE [sspdw-md.md](../../includes/sspdw-md.md)] ```syntaxsql 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)](../../t-sql/statements/alter-database-transact-sql.md). The following table shows how the setting of `ANSI_NULLS` affects the results of Boolean expressions using null and non-null values. |Boolean Expression|**SET ANSI_NULLS ON**|**SET ANSI_NULLS OFF**| |---------------|---------------|------------| | `NULL = NULL` |UNKNOWN|TRUE| | `1 = NULL` |UNKNOWN|FALSE| | `NULL <> NULL` |UNKNOWN|FALSE| | `1 <> NULL` |UNKNOWN|TRUE| | `NULL > NULL` |UNKNOWN|UNKNOWN| | `1 > NULL` |UNKNOWN|UNKNOWN| | `NULL IS NULL` |TRUE|TRUE| | `1 IS NULL` |FALSE|FALSE| | `NULL IS NOT NULL` |FALSE|FALSE| | `1 IS NOT NULL` |TRUE|TRUE| `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. [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] 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, [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] 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. > [!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` must also be set to ON, and `NUMERIC_ROUNDABORT` must be set to OFF. The [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] Native Client ODBC driver and [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] Native Client OLE DB Provider for [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] 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 [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)]. 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: ```sql 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. ```sql 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. | EqualNull | DifferentNull | EqualZero | DifferentZero | | --- | --- | --- | --- | | True | False | False | True | 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`. ```sql -- 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. ```sql 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. ```sql 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)](../../t-sql/statements/set-statements-transact-sql.md) - [SESSIONPROPERTY (Transact-SQL)](../../t-sql/functions/sessionproperty-transact-sql.md) - [= (Equals) (Transact-SQL)](../../t-sql/language-elements/equals-transact-sql.md) - [IF...ELSE (Transact-SQL)](../../t-sql/language-elements/if-else-transact-sql.md) - [<> (Not Equal To) (Transact-SQL)](../../t-sql/language-elements/not-equal-to-transact-sql-traditional.md) - [SET ANSI_DEFAULTS (Transact-SQL)](../../t-sql/statements/set-ansi-defaults-transact-sql.md) - [WHERE (Transact-SQL)](../../t-sql/queries/where-transact-sql.md) - [WHILE (Transact-SQL)](../../t-sql/language-elements/while-transact-sql.md)