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.

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:
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.

Ensure correct configuration of CONCAT_NULLS_YIELDS_NULL setting to maintain compatibility with future SQL Server versions.
Follow these steps to address the issue:
-
Verify the current setting for CONCAT_NULLS_YIELDS_NULL using a query. If the setting is OFF, they need to be corrected.
-
Open SQL Server Management Studio (SSMS) and navigate to each affected database.
-
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:
1ALTER DATABASE DatabaseName SET CONCAT_NULLS_YIELDS_NULL ON;

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

Rule has no parameters.

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


Design Rules, Deprecated Features, Code Smells

