In SQL Server, the ANSI_NULLS setting allows you to specify how NULL values are treated in queries.
More specifically, it allows you to specify ISO compliant behaviour of the Equal To (=) and Not Equal To (<>) comparison operators when they are used with NULL values.
ANSI_NULLS can be set to ON or OFF. A NULL test that returns true with ANSI_NULLS OFF might actually return false with ANSI_NULLS ON.
This can be the source of much confusion, and so it pays to understand exactly how ANSI_NULLS works.
ANSI_NULLS settings can be set at the database level and at the session level. If an ANSI_NULLS setting at the session level isn’t specified, SQL Server will use whichever ANSI_NULLS setting is applied to the current database. Therefore, you can override the database setting with your own session level setting when writing ad hoc queries.
An important thing to note is that 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.
How to Check your Session’s ANSI_NULLS Setting
You can use the SESSIONPROPERTY() function to check the ANSI_NULLS setting for the current session.
SELECT SESSIONPROPERTY('ANSI_NULLS');Result:
+--------------------+ | (No column name) | |--------------------| | 1 | +--------------------+
In this case, the ANSI_NULLS setting for my session is ON.
A zero (0) would mean that it’s off.
How to Change your Session’s ANSI_NULLS Setting
You can set your session’s ANSI_NULLS setting to OFF with the following code:
SET ANSI_NULLS OFF;Then checking it again will produce a zero.
SELECT SESSIONPROPERTY('ANSI_NULLS');Result:
+--------------------+ | (No column name) | |--------------------| | 0 | +--------------------+
The default value for SET ANSI_NULLS is OFF. However, as mentioned above, 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.
Examples of How ANSI_NULLS Affects Queries
Here are some basic examples to demonstrate the different results you can get, depending on the value of the ANSI_NULLS setting.
These use SET ANSI_NULLS to toggle the ANSI_NULLS setting for the current session.
ANSI_NULLS ON
SET ANSI_NULLS ON;
SELECT NULL
WHERE NULL = NULL;Result:
(0 rows affected)
When ANSI_NULLS is ON, all comparisons against a NULL value evaluate to UNKNOWN.
In this case, we can’t truely say that NULL equals NULL because each value is unknown.
Therefore, no rows are returned for the above query.
ANSI_NULLS OFF
SET ANSI_NULLS OFF;
SELECT NULL
WHERE NULL = NULL;Result:
+--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+
When ANSI_NULLS is OFF, comparisons of all data against a NULL value evaluate to TRUE if the data value is NULL.
The same logic applies when using the Not Equal To operator (<>).
Let’s expand the example to include the Not Equal To operator (<>), as well as a comparison between NULL and a non-NULL value.
ANSI_NULLS ON
SET ANSI_NULLS ON;
SELECT NULL
WHERE NULL = NULL;
SELECT 'Not NULL'
WHERE NULL <> NULL;
SELECT NULL
WHERE 1 = NULL;
SELECT 'Not NULL'
WHERE 1 <> NULL;Result:
(0 rows affected) (0 rows affected) (0 rows affected) (0 rows affected)
As expected, no rows are returned for any of the queries. This is because NULL values are treated as an UNKNOWN value when ANSI_NULLS is ON.
ANSI_NULLS OFF
SET ANSI_NULLS OFF;
SELECT NULL
WHERE NULL = NULL;
SELECT 'Not NULL'
WHERE NULL <> NULL;
SELECT NULL
WHERE 1 = NULL;
SELECT 'Not NULL'
WHERE 1 <> NULL;Result:
+--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) (0 rows affected) (0 rows affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected)
We get a different result when ANSI_NULLS is OFF.
In this case, SQL Server doesn’t treat NULL as UNKNOWN. It determines that NULL is in fact equal to NULL.
This is not compliant with the ANSI standard.
The IS NULL Predicate
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
Here’s what happens when we rewrite the previous example to use IS NULL and IS NOT NULL.
ANSI_NULLS ON
SET ANSI_NULLS ON;
SELECT NULL
WHERE NULL IS NULL;
SELECT NULL
WHERE NULL IS NOT NULL;
SELECT 'Not NULL'
WHERE 1 IS NULL;
SELECT 'Not NULL'
WHERE 1 IS NOT NULL;Result:
+--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) (0 rows affected) (0 rows affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected)
ANSI_NULLS OFF
SET ANSI_NULLS OFF;
SELECT NULL
WHERE NULL IS NULL;
SELECT NULL
WHERE NULL IS NOT NULL;
SELECT 'Not NULL'
WHERE 1 IS NULL;
SELECT 'Not NULL'
WHERE 1 IS NOT NULL;Result:
+--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) (0 rows affected) (0 rows affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected)
As expected, we get the same result regardless of the ANSI_NULLS setting.
Comparison Table
The following table outlines the variations you can get depending on the Boolean expression and ANSI_NULLS setting.
| 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 |
Setting ANSI_NULLS at the Database Level
Each SQL Server database has an ANSI_NULLS setting, which determines how comparisons to NULL values are evaluated.
- When set to
ON, comparisons to aNULLvalue evaluate toUNKNOWN. - When set to
OFF, comparisons of non-Unicode values to aNULLvalue evaluate toTRUEif both values areNULL.
You can change this setting on a database with the following code:
ALTER DATABASE CURRENT
SET ANSI_NULLS ON;That sets ANSI_NULLS to ON for the current database. You can swap CURRENT with the name of a database if preferred.
You can check the current setting with the DATABASEPROPERTYEX() function.
SELECT DATABASEPROPERTYEX('Music','IsAnsiNullsEnabled');Result:
1
As mentioned, you can override this setting when writing ad hoc queries by setting it at the session level like we did earlier.
While we’re on the topic, I should mention that SQL Server databases also have an ANSI_NULL_DEFAULT setting. This setting determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability isn’t explicitly defined in CREATE TABLE or ALTER TABLE statements.
This value can be set like this:
ALTER DATABASE CURRENT
SET ANSI_NULL_DEFAULT ON;Its value can be retrieved like this:
SELECT DATABASEPROPERTYEX('Music','IsAnsiNullDefault');Result:
1
You can also use the sys.databases catalog view to return these settings for all databases.
SELECT
name,
is_ansi_nulls_on,
is_ansi_null_default_on
FROM sys.databases
ORDER BY name ASC;