SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

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.

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.

-- 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

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 the 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

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irespectively 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

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

About these ads

One thought on “SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

  1. 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

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