SET CONCAT_NULL_YIELDS_NULL in SQL Server

feature-sql1-618x350

How often do we think of NULL values while concatenating NULLs with string values? String concentration with NULL values can give us some unexpected results if we are not aware of CONCAT_NULL_YIELDS_NULL database SET options.

CONCAT_NULL_YIELDS_NULL options control whether concatenation results shall be treated as NULL or empty string values.

An Important Note from SQL Server Books Online

 

In a future version of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON, and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Syntax

 

SET CONCAT_NULL_YIELDS_NULL { ON | OFF } 

 

Description

 

SET CONCAT_NULL_YIELDS_NULL ON

 

When the CONCAT_NULL_YIELDS_NULL database option in ON, concatenating a string value with NULL will result in NULL as an output. When we have CONCAT_NULL_YIELDS_NULL as ON, then NULL values are considered as UNKNOWN, hence concatenating a string value with UNKNOWN yields in UNKNOWN, NULL as output.

Example:

SET CONCAT_NULL_YIELDS_NULL ON; 
SELECT 'tech-recipes' + NULL; --results in NULL. 

 

CONCAT_NULL_YIELDS_NULL_ON

SET CONCAT_NULL_YIELDS_NULL OFF

 

When the CONCAT_NULL_YIELDS_NULL database option in OFF, concatenating a string value with NULL will return a string as output. When we have CONCAT_NULL_YIELDS_NULL as OFF, NULL values are considered as an empty string, hence concatenating a string value with empty string yields in a string as output because it is considered as TRUE instead of UNKNOWN.

Example:

SET CONCAT_NULL_YIELDS_NULL OFF; 
SELECT 'tech-recipes' + NULL; --results in 'tech-recipes'. 

 

CONCAT_NULL_YIELDS_NULL_OFF

In future versions of SQL Server, the CONCAT_NULL_YIELDS_NULL set option will be deprecated. Therefore, we should avoid using it in future development work. We can use the ISNULL or COALESCE function to handle the behavior of NULL values while concatenating strings. It is also advisable to use CONCAT function to concat strings introduced with SQL Server 2012 edition which handles NULL values concatenation perfectly.

Use the CONCAT / ISNULL/ COALESCE Function Instead of CONCAT_NULL_YIELDS_NULL

 

The following examples must be followed while writing code instead of using CONCAT_NULL_YIELDS_NULL database options while concatenating strings.

DECLARE @MyVar1 as VARCHAR( 50) = 'You are ' ,
        @MyVar2 as VARCHAR (50) = NULL,
        @MyVar3 as VARCHAR (50) = 'on tech-recipes.com';

SELECT CONCAT (@MyVar1, @MyVar2, @MyVar3 );

 

CONCAT_NULL_Values_SQL_Server

In the example above, we are using the CONCAT function introduced with SQL Server 2012. It will treat NULL in @MyVar2 variable as an empty string while concatenating strings. It is always better to use this function instead of playing with the CONCAT_NULL_YIELDS_NULL set option to avoid any code changes as this setting is going to be deprecated.

DECLARE @MyVar1 as VARCHAR( 50) = 'You are ',
        @MyVar2 as VARCHAR (50) = NULL,
        @MyVar3 as VARCHAR (50) = 'on tech-recipes.com';


SELECT ISNULL(@MyVar1,'') + ISNULL(@MyVar2,'') + ISNULL(@MyVar3,'');

ISNULL_SQL_Server_Avoid_NULL

In the example above, we are using the ISNULL function to check and replace any NULL occurrence with an empty string instead of using the CONCAT_NULL_YIELDS_NULL setting. We can use the ANSI function COALESCE as well instead of ISNULL.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.