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:

SQL SERVER - A Quick Note on CONCAT_NULL_YIELDS_NULL concatstring1

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:

SQL SERVER - A Quick Note on CONCAT_NULL_YIELDS_NULL concatstring2

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.

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

SQL Function
Previous Post
SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes
Next Post
SQL SERVER – Weekly Series – Memory Lane – #036

Related Posts

12 Comments. Leave new

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

    Reply
  • Prashant Shinde
    July 5, 2013 4:21 pm

    Very nice inputs as usual from Pinal……Thanks again :)

    Reply
  • Deepak Kumar
    July 6, 2013 7:47 am

    Thank you very much again new knowledge. I m always getting new about sql.

    Reply
  • Andre Ranieri
    July 6, 2013 8:07 pm

    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?

    Reply
  • Anirudh Gupta
    July 7, 2013 12:14 am

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

    Reply
  • Marimuthu Kandasamy
    July 9, 2013 3:04 pm

    Thank you so much for making us to know the new things SQL…

    Reply
  • I think we can use CONCAT function from 2012 and beyond

    Reply
  • its very useful artical. thank you

    Reply
  • Hi Pinal

    I’ve seen the SET CONCAT_NULL_YIELDS_NULL frequently executed in between 7 and 12 seconds on a production SQL 2008R2. Most of the time is in a matter of milliseconds. Any ideas what could cause this?

    Reply

Leave a Reply