SQL SERVER – A Quick Note on CONCAT_NULL_YIELDS_NULL

Recently one of my friends sent me a SQL script to debug and I noticed that above all of his scripts he was executed following query.

SET CONCAT_NULL_YIELDS_NULL OFF;

This made me curious and I asked him reason why is he executes above script. He answered that when his application have few columns which when he concats return the value as zero because his column contains NULL values. This made me curious as I believe if he has such business needs he should have changed his columns to allow NOT Null. When asked he suggested that he can’t do that as well due to his database structure is following some legacy standard and system.

Well, let us quickly understand his scenario with following small reproduction script.

DECLARE @VAR1 VARCHAR(100)
SET @VAR1 = 'First'
DECLARE @VAR2 VARCHAR(100)
SET @VAR2 = 'Second'
DECLARE @VAR3 VARCHAR(100)
SET @VAR3 = 'Third'
DECLARE @VAR4 VARCHAR(100)
SET @VAR4 = NULL
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @VAR1 + @VAR2 + @VAR3 + @VAR4 AS ConcatString
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @VAR1 + @VAR2 + @VAR3 + @VAR4 AS ConcatString

When you execute above scripts you get following resultset:

When you run above script it is very clear that he is setting the values CONCAT_NULL_YIELDS_NULL because one of the variable (in his case column) contains a NULL value and he can’t have that NULL value when he is concating the values. As NULL when concats with another value it returns NULL as well. In his case, he wanted to ignore any value when they are NULL and does not convert the value of the entire string to NULL.

When I looked at the script, I felt that he is over engineering everything. When I asked him why he is not using ISNULL function and he was not sure about it. I think it is not a good idea to set the value of CONCAT_NULL_YIELDS_NULL to OFF as that is not going to be supported in future versions of the SQL. If you start writing code and script which is based the settings of CONCAT_NULL_YIELDS_NULL, there is a good chance in future that you will have to re-write every script of your application to accommodate the change. As per the Microsoft Documentation in the future the value of CONCAT_NULL_YIELDS_NULL will be always ON.

It will be a good idea to re-write above script with ISNULL function and let the value of the setting CONCAT_NULL_YIELDS_NULL to be always true.

DECLARE @VAR1 VARCHAR(100)
SET @VAR1 = 'First'
DECLARE @VAR2 VARCHAR(100)
SET @VAR2 = 'Second'
DECLARE @VAR3 VARCHAR(100)
SET @VAR3 = 'Third'
DECLARE @VAR4 VARCHAR(100)
SET @VAR4 = NULL
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT ISNULL(@VAR1,'') + ISNULL(@VAR2,'') + ISNULL(@VAR3,'') + ISNULL(@VAR4,'') AS ConcatString
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT ISNULL(@VAR1,'') + ISNULL(@VAR2,'') + ISNULL(@VAR3,'') + ISNULL(@VAR4,'') AS ConcatString

When you execute above scripts you get following resultset:

When you execute above script you will notice that it does not matter what is the settings of the option CONCAT_NULL_YIELDS_NULL  the result set is now predictable and NULL is ignored in resultset.

NOTE: If your business need is such that you always want to return NULL when it is concated with another value, you should not play with settings CONCAT_NULL_YIELDS_NULL  as well should not use ISNULL function.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – A Quick Note on CONCAT_NULL_YIELDS_NULL

  1. we can use this method also
    SELECT COALESCE(@VAR1,”)+COALESCE(@VAR2,”)+COALESCE(@VAR3,”)+COALESCE(@VAR4,”)AS ConcatString

  2. I inherited a vendor’s customer relations management database that was built at or prior to SQL 2000. The customer table has the address split into columns for each component part, StreetNumber, StreetPrefix, StreetName, StreetSuffix, StreetPost. This is to facilitate bulk mailing through the US Postal Service. However, every report or customer list queried from the database has to concatenate these component parts back together. CONCAT_NULL_YIELDS_NULL OFF has been a useful tool for these types of queries, but of course now it’s deprecated. I’m writing any new queries to COALESCE the null values to blank in anticipation of this; however, at some point in the future all existing queries will have to be rewritten.

    Is there any reason the SQL team would have deprecated CONCAT_NULL_YIELDS_NULL? Is it a very expensive operator, or is there some other logic to the decision?

  3. ISNULL function always be the good option………obviously summation or conactenation must need a ISnull function…

  4. Pingback: SQL SERVER – Check Database Level (IsNullConcat) and Session Level Settings (CONCAT_NULL_YIELDS_NULL) using T-SQL | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s