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.

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

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:

• SQL Server – 2016 – New Feature: Dynamic Data Masking
• SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause
• SQL SERVER 2016 – Comparing Execution Plans
• SQL SERVER – 2016 – Opening JSON with OPENJSON()

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)