SQL Server has an ANSI_NULLS setting that determines how NULL values are evaluated when compared to another value with the Equals (=) and Not Equal To (<>) comparison operators.

While it’s true that you can change the ANSI_NULLS setting at the session level (using SET ANSI_NULLS), each database also has its own ANSI_NULLS setting.

You can check your database to see whether its ANSI_NULLS setting is ON or OFF.

To do this with T-SQL, you can either use the sys.databases catalog view or the DATABASEPROPERTYEX() function.

The sys.databases View

The sys.databases catalog view contains a lot of information about each database in your SQL Server instance.

The following query returns the ANSI_NULLS setting for the Music database:

SELECT is_ansi_nulls_on 
FROM sys.databases
WHERE name = 'Music';

Result:

+--------------------+
| is_ansi_nulls_on   |
|--------------------|
| 1                  |
+--------------------+

In this case, ANSI_NULLS is ON for this database.

We can turn it OFF like this:

ALTER DATABASE Music
SET ANSI_NULLS OFF;

You can eliminate the WHERE clause when using the sys.databases catalog view to return data for all databases. Like this:

SELECT 
    name,
    is_ansi_nulls_on 
FROM sys.databases
ORDER BY name ASC;

This view also has a column called is_ansi_null_default_on, which returns the ANSI_NULL_DEFAULT setting for the database.

The ANSI_NULL_DEFAULT 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.

We could modify the previous example to include this column:

SELECT
    name,
    is_ansi_nulls_on,
    is_ansi_null_default_on
FROM sys.databases
ORDER BY name ASC;

The DATABASEPROPERTYEX() Function

Another way to check these settings is with the the DATABASEPROPERTYEX() function.

Here’s how to check the ANSI_NULLS setting for the Music DB:

SELECT DATABASEPROPERTYEX('Music','IsAnsiNullsEnabled');

Result:

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

It’s now 0 for OFF because I set it to OFF in a previous example.

To check the ANSI_NULL_DEFAULT setting, do this:

SELECT DATABASEPROPERTYEX('Music','IsAnsiNullDefault');

Result:

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