SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated |
![]() |
The topic describes the SA0155B analysis rule.

Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

The rule checks T-SQL code for setting of CONCAT_NULL_YIELDS_NULL options to OFF.
In a future version of Microsoft SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will produce an error.
To view the current setting for this setting, run the following query.
DECLARE @CONCAT_NULL_YIELDS_NULL VARCHAR(3) = ‘OFF’;
IF ( (4096 & @@OPTIONS) = 4096 ) SET @CONCAT_NULL_YIELDS_NULL = ‘ON’;
SELECT @CONCAT_NULL_YIELDS_NULL AS CONCAT_NULL_YIELDS_NULL;

Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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 |
