SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

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:

1-- Example of problematic setting
2SET CONCAT_NULL_YIELDS_NULL OFF;
3SELECT '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.

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:

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

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Deprecated Features, Code Smells

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Other Resources