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:
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.

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:
-
Identify any queries or stored procedures where SET CONCAT_NULL_YIELDS_NULL OFF is used.
-
Replace any instance of SET CONCAT_NULL_YIELDS_NULL OFF with SET CONCAT_NULL_YIELDS_NULL ON.
-
Test the modified queries to ensure that string concatenations involving NULL values produce the expected NULL results.
-
Establish development practices to maintain the CONCAT_NULL_YIELDS_NULL setting to ON by default.
For example:
1SET CONCAT_NULL_YIELDS_NULL ON; 2SELECT '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.


Deprecated Features, Bugs


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 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 |

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 |
