SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

Setting CONCAT_NULL_YIELDS_NULL to OFF in T-SQL code may cause errors in future SQL Server versions, as this option will default to ON.

Description

When the CONCAT_NULL_YIELDS_NULL option is set to OFF in T-SQL script, it affects how string concatenations involving NULL values are handled. Turning this option off allows the concatenation of a NULL with a string to result in the string itself, ignoring the NULL. However, best practices entail keeping this option ON, as future SQL Server versions will default to this setting, and explicit OFF settings will result in errors.

For example:

SQL
Copy
1-- Example of problematic setting
2SET CONCAT_NULL_YIELDS_NULL OFF;
3SELECT 'Hello' + NULL;

This example returns ‘Hello’ instead of NULL because CONCAT_NULL_YIELDS_NULL is set to OFF. The resulting behavior can lead to unexpected data processing outcomes and will cause errors in future SQL Server versions.

  • Existing applications may malfunction as they transition to newer SQL Server versions, generating runtime errors due to this setting being deprecated.

  • Data integrity can be compromised due to unexpected concatenation results when integrating applications across different SQL Server setups.

How to fix

Setting CONCAT_NULL_YIELDS_NULL to OFF in T-SQL code may cause errors in future SQL Server versions, as this option will default to ON.

Follow these steps to address the issue:

  1. Identify any queries or stored procedures where SET CONCAT_NULL_YIELDS_NULL OFF is used.

  2. Replace any instance of SET CONCAT_NULL_YIELDS_NULL OFF with SET CONCAT_NULL_YIELDS_NULL ON.

  3. Test the modified queries to ensure that string concatenations involving NULL values produce the expected NULL results.

  4. Establish development practices to maintain the CONCAT_NULL_YIELDS_NULL setting to ON by default.

For example:

SQL
Copy
1SET CONCAT_NULL_YIELDS_NULL ON;
2SELECT 'Hello' + NULL; -- This will return NULL as expected

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.

Effort To Fix
20 minutes per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 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
 7SET CONCAT_NULL_YIELDS_NULL OFF
 8
 9SET ANSI_PADDING ON
10
11SET CONCAT_NULL_YIELDS_NULL,ANSI_PADDING OFF 
12
13SET ANSI_NULLS OFF
14
15SET ANSI_PADDING OFF
16
17SET CONCAT_NULL_YIELDS_NULL OFF
18
19SET ANSI_DEFAULTS OFF
Analysis Results
  Message Line Column
1 SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated. 7 4
2 SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated. 11 4
3 SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated. 17 4
See Also

Other Resources

Analysis Rules