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)
