Click or drag to resize

SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

The topic describes the SA0155B analysis rule.

Message

Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

Description

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.

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

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;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
 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

Analysis Results

 MessageLineColumn
1SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.526
2SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.75
3SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.125
4SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated.194
See Also

Other Resources