SA0207 : Setting ANSI_NULLS to OFF is deprecated

Introduction

Proper management of ANSI_NULLS settings is essential to prevent future compatibility issues in SQL Server.

Description

In SQL Server, the ANSI_NULLS setting controls how SQL Server handles comparisons with null values. If ANSI_NULLS is set to OFF , SQL Server uses non-standard behavior in null comparisons, which can lead to unanticipated results in queries. In future versions of SQL Server, this setting will be permanently ON , meaning any code setting it to OFF could result in errors, breaking existing applications.

For example:

-- Example where ANSI_NULLS is set to OFF
SET ANSI_NULLS OFF;
SELECT * FROM Employees WHERE Salary = NULL;

This query intends to find employees with a null salary. With ANSI_NULLS set to OFF , the expected behavior is changed, potentially leading to no results because SQL Server does not use the standard evaluation for nulls.

Incorrect handling of null comparisons may produce unexpected results in query outputs, affecting data integrity and application logic.
Dependencies on deprecated settings such as ANSI_NULLS OFF can lead to future compatibility issues as SQL Server evolves.

How to fix

Ensure that the ANSI_NULLS setting is set to ON in all T-SQL scripts to adhere to SQL Server best practices and future compatibility.

Follow these steps to address the issue:

1.Identify any existing T-SQL scripts where SET ANSI_NULLS is set to OFF.

2.Modify these scripts to set SET ANSI_NULLS ON at the beginning of each script or session.

3.Review application logic that relies on SET ANSI_NULLS OFF behavior and adjust it to work with SET ANSI_NULLS ON .

4.Test the modified scripts to ensure they behave as expected with SET ANSI_NULLS ON , paying special attention to queries involving null comparisons.

5.Update documentation and database practices to avoid the use of SET ANSI_NULLS OFF in future development.

For example:

-- Corrected query with ANSI_NULLS set to ON
SET ANSI_NULLS ON;
SELECT * FROM Employees WHERE Salary IS NULL;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

ALTER DATABASE TestDb SET ANSI_NULLS OFF

ALTER DATABASE TestDb SET ANSI_PADDING OFF

ALTER DATABASE TestDb SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL OFF

SET ANSI_PADDING ON

SET CONCAT_NULL_YIELDS_NULL,ANSI_PADDING OFF

SET ANSI_NULLS OFF

SET ANSI_PADDING OFF

SET CONCAT_NULL_YIELDS_NULL OFF

SET ANSI_DEFAULTS OFF

Analysis Results

	Message	Line	Column
1	SA0207 : Setting ANSI_NULLS to OFF is deprecated.	1	26
2	SA0207 : Setting ANSI_NULLS to OFF is deprecated.	13	4
3	SA0207 : Setting ANSI_NULLS to OFF is deprecated.	19	4

Analysis Rules