SA0155 : Deprecated setting of database option CONCAT_NULL_YIELDS_NULL to OFF

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:

SQL
1-- Example of a query checking these setting
2SELECT name, is_concat_null_yields_null_on 
3FROM sys.databases
4WHERE 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:

SQL
1ALTER 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.
Categories

Design Rules, Deprecated Features, Code Smells

Additional Information
See Also

Other Resources