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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
we can use this method also
SELECT COALESCE(@VAR1,”)+COALESCE(@VAR2,”)+COALESCE(@VAR3,”)+COALESCE(@VAR4,”)AS ConcatString
Thanks! This is indeed a good one.
Very nice inputs as usual from Pinal……Thanks again :)
Thank you very much again new knowledge. I m always getting new about sql.
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?
ISNULL function always be the good option………obviously summation or conactenation must need a ISnull function…
Thank you so much for making us to know the new things SQL…
I think we can use CONCAT function from 2012 and beyond
Yes. You can use
its very useful artical. thank you
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?
What? SET statement take 12 seconds? I have never seen this earlier.