What Does ANSI_NULLS Do?

Have you ever seen ANSI_NULLS being set to ON or OFF in a script or stored procedure? I’ve seen or heard it mentioned a few times recently and thought it would be worth demonstrating an example of what this setting does.

Defining ANSI_NULLS

In simple terms, this setting affects how expressions with NULL values are handled. They can be TRUE, FALSE, or UNKNOWN.

If you have an open query window and you’re curious about whether or not your session has ANSI_NULLS set to ON or OFF, you can go to the menu bar to select Query > Query Options > Execution > ANSI.

On Again, Off Again

Let’s test out how a query performs with ANSI_NULLS set to ON and how that compares to when ANSI_NULLS is set to OFF. Using the Stackoverflow2013 database, we’ll run a query to give us the Id, DisplayName, and Location for the user with an ID of 11.

This is the record we’ll check:

SELECT Id, DisplayName, Location
FROM Users
WHERE ID = 11;
GO

Note that Location is NULL.

Let’s run the following script that checks where the WebsiteUrl equals @TestUrl, which has been set to NULL.

SET ANSI_NULLS OFF;

DECLARE @TestUrl VARCHAR(20);
SET @TestUrl = NULL;

SELECT Id, DisplayName, Location
FROM Users
WHERE ID = 11 AND WebsiteUrl = @TestUrl;
GO

Since ANSI_NULLS is set to OFF, SQL Server evaluates the WebsiteUrl = @TestUrl as TRUE and returns our result. What if we set ANSI_NULLS to ON and run the same query again?

SET ANSI_NULLS ON;

DECLARE @TestUrl VARCHAR(20);
SET @TestUrl = NULL;

SELECT Id, DisplayName, Location
FROM Users
WHERE ID = 11 AND WebsiteUrl = @TestUrl;
GO

With ANSI_NULLS set to ON, our query does not return a record. Instead of recognizing NULL = NULL as TRUE, it is now considered to be UNKNOWN.

Tip of the Iceberg

We looked at an example of NULL = NULL. This is just one of many possible scenarios you can run into with NULL comparisons and ANSI_NULLS. To read more about how different comparisons are handled, here is a chart from the ANSI_NULLS documentation:

As you can see, there are plenty of combinations to consider.

Thanks for reading!