SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated
Introduction
Section titled “Introduction”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
Section titled “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:
-- Example of problematic settingSET 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.
How to fix
Section titled “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:
SET CONCAT_NULL_YIELDS_NULL ON;SELECT 'Hello' + NULL; -- This will return NULL as expectedThe rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”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 OFFAnalysis Results
Section titled “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 |