In SQL Server, the ANSI_NULLS setting allows you to specify how NULL values are treated in queries.

You can use the SESSIONPROPERTY() function to check the ANSI_NULLS setting for the current session.

Example

Here’s an example to demonstrate.

SELECT SESSIONPROPERTY('ANSI_NULLS');

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 1                  |
+--------------------+

In this case, the ANSI_NULLS setting for my session is ON.

You can set it to OFF with the following code:

SET ANSI_NULLS OFF;

Then checking it again will produce a zero (meaning OFF).

SELECT SESSIONPROPERTY('ANSI_NULLS');

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 0                  |
+--------------------+

The default value for SET ANSI_NULLS is OFF.

However, 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.

ANSI_NULLS are also specified at the database level. However, your session setting overrides the database level setting. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies.