SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

Introduction

Ensure the CONCAT_NULL_YIELDS_NULL setting is not set to OFF in T-SQL code to prevent future compatibility issues.

Description

In Microsoft SQL Server, the CONCAT_NULL_YIELDS_NULL setting determines the behavior of string concatenation involving NULL values. When set to OFF , concatenating a string with NULL results in the original string, whereas when set to ON , it results in NULL . Future versions of SQL Server will always have CONCAT_NULL_YIELDS_NULL set to ON , and any attempt to set it to OFF will produce an error.

For example:

-- Example of problematic setting
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'Hello' + NULL AS Result;

With CONCAT_NULL_YIELDS_NULL set to OFF , this query returns ‘Hello’. However, future SQL Server versions will default to ON , causing the query to return NULL , which may lead to unexpected results in applications.

Setting CONCAT_NULL_YIELDS_NULL to OFF can cause compatibility issues in future SQL Server releases.
Applications relying on this setting being OFF might encounter errors or altered behavior.

How to fix

Ensure compatibility with future SQL Server versions by setting CONCAT_NULL_YIELDS_NULL to ON.

Follow these steps to address the issue:

1.Identify T-SQL scripts or stored procedures where CONCAT_NULL_YIELDS_NULL is set to OFF using a search in your database scripts or SSMS.

2.Modify the identified scripts to remove the line SET CONCAT_NULL_YIELDS_NULL OFF or replace it with SET CONCAT_NULL_YIELDS_NULL ON .

3.Ensure application logic that depends on this setting is adjusted to handle NULL concatenation consistently with CONCAT_NULL_YIELDS_NULL set to ON.

For example:

-- Correct setting to ensure future compatibility
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'Hello' + NULL AS Result;

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

13 minutes per issue.

Additional Information

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

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


--SET OFFSETS

Analysis Results

	Message	Line	Column
1	SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.	5	26
2	SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.	7	5
3	SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.	12	5
4	SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.	19	4

Analysis Rules