Skip to content

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.

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:

-- Example of problematic setting
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT '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.

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:

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'Hello' + NULL; -- This will return NULL as expected

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.

20 minutes per issue.

Deprecated Features, Bugs

Deprecated Database Engine Features in SQL Server 2017

ALTER DATABASE TestDb SET ANSI_NULLS OFF
ALTER DATABASE TestDb SET ANSI_PADDING OFF
ALTER DATABASE TestDb SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_PADDING ON
SET CONCAT_NULL_YIELDS_NULL,ANSI_PADDING OFF
SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_DEFAULTS OFF
  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

Analysis Rules