SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT

Have you ever worked with CONCAT_NULL_YIELDS_NULL earlier in your career? If yes, you will find this post very interesting. If you have not worked with this function before I think this post will teach you something very interesting for CONCAT.

SQL SERVER - Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT concat-800x163

Before we start please note that as per SQL Server official documentation CONCAT_NULL_YIELDS_NULL will be always ON in the future so avoid setting it off. What this function essentially does is that when it is on it will return a null value when any other value is connected it with using + operator. If CONCAT_NULL_YIELDS_NULL is set to OFF it will return the original value instead of NULL.

Let us see a simple example of the same.

Solarwinds
-- Before SQL Server 2012
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO

SQL SERVER - Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT CONCAT_NULL_YIELDS_NULL1

However, in SQL Server 2012 the behavior of the ‘+’ operator remains the same but the behavior of function CONCAT is bit different and it totally ignores this value. Here is an example of it.

--- SQL Server 2012 and onwards
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO

SQL SERVER - Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT CONCAT_NULL_YIELDS_NULL2

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irrespectively I find it very interesting how CONCAT function works with NULLs.

Read more about CONCAT function over here as well you can watch the video about how the concrete function works with SQL Server 2012.

String Function – CONCAT() – A Quick Introduction

Here is a few additional blog post on this subject:

Here is another interesting post where I discuss Compress and Decompress: SQL SERVER 2016 – New T-SQL Functions – COMPRESS – DECOMPRESS. With every release of SQL Server, Microsoft has been adding enhancements to the product. Earlier I have written below blogs talking about new features/enhancements. Now I will write about features for SQL Server which are COMPRESS – DECOMPRESS.

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #028
Next Post
SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

Related Posts

4 Comments. Leave new

  • Thanks, nice observation. I had a look into BOL and it’s mentioned there “Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned”. This makes clear why the session property hasn’t any impact for the CONCAT function.

    Thanks & Regards
    Rob

    Reply
  • Thanks Pinal,
    but I have a problem about number fields.

    I hope you help me

    example;

    –DROP TABLE t;

    CREATE TABLE t ( col1 INT, col2 INT);

    INSERT INTO t (col1, col2) VALUES (100, NULL);
    INSERT INTO t (col1, col2) VALUES (NULL,NULL);

    SELECT * FROM t;

    SELECT SUM(col1) , SUM(col2) FROM t

    –SET CONCAT_NULL_YIELDS_NULL OFF;

    SELECT SUM(col1) + SUM(col2) FROM t

    Reply
    • Hi again, I want to explain some points,

      I cant use ISNULL. because there is this sum+sum function in a COMPUTE division of SHAPE-APPEND ADO query.

      it doesnt accept ISNULL :(

      I need to a SET for numbers similar of CONCAT_NULL_YIELDS_NULL

      thanks

      Reply

Leave a Reply

Menu