SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated |
![]() |
Ensure the CONCAT_NULL_YIELDS_NULL setting is not set to OFF in T-SQL code to prevent future compatibility issues.

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:
-
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.
-
Modify the identified scripts to remove the line SET CONCAT_NULL_YIELDS_NULL OFF or replace it with SET CONCAT_NULL_YIELDS_NULL ON.
-
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


SQL
1ALTER DATABASE TestDb SET ANSI_NULLS OFF 2 3ALTER DATABASE TestDb SET ANSI_PADDING OFF 4 5ALTER DATABASE TestDb SET CONCAT_NULL_YIELDS_NULL OFF 6 7 set CONCAT_NULL_YIELDS_NULL off 8 9 set ansi_padding on 10 11 12 set CONCAT_NULL_YIELDS_NULL,ANSI_PADDING off 13 14 15SET ANSI_NULLS OFF 16 17SET ANSI_PADDING OFF 18 19SET CONCAT_NULL_YIELDS_NULL OFF 20 21SET ANSI_DEFAULTS OFF 22 23 24--SET OFFSETS |

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 |
