SA0155 : Deprecated setting of database option CONCAT_NULL_YIELDS_NULL to OFF

Introduction

Incorrect setting for CONCAT_NULL_YIELDS_NULL can lead to unexpected query behavior and compatibility issues.

Description

In SQL Server, improper configuration of certain database options can lead to future compatibility issues. Specifically, when ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL are set to OFF, it could cause problems because upcoming versions of SQL Server will always require ANSI_PADDING to be ON.

For example:

-- Example of a query checking these setting
SELECT name, is_concat_null_yields_null_on
FROM sys.databases
WHERE is_concat_null_yields_null_on = 0;

This query highlights databases with potentially problematic configurations. Maintaining the default setting is important for avoiding unexpected errors and ensuring future compatibility.

Applications relying on CONCAT_NULLS_YIELDS_NULL being OFF may encounter runtime errors or behave unexpectedly when null values are concatenated.

How to fix

Ensure correct configuration of CONCAT_NULLS_YIELDS_NULL setting to maintain compatibility with future SQL Server versions.

Follow these steps to address the issue:

1.Verify the current setting for CONCAT_NULLS_YIELDS_NULL using a query. If the setting is OFF, they need to be corrected.

2.Open SQL Server Management Studio (SSMS) and navigate to each affected database.

3.For each database, turn on the required setting by using ALTER DATABASE statement. Set CONCAT_NULLS_YIELDS_NULL to ON.

Example of updated database setting:

ALTER DATABASE DatabaseName SET CONCAT_NULLS_YIELDS_NULL ON;

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

13 minutes per issue.

Additional Information

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Analysis Rules