CONCAT_NULL_YIELDS_NULL

Written by

Introduction

In this blog post, we will see What is CONCAT_NULL_YIELDS_NULL and what’s the syntax to use and what are default settings of it in both Azure SQL Db and On-Premises DB. Also we will see what are the important things to know about it and how to handle our codes irrespective of CONCAT_NULL_YIELDS_NULL setting changes.

CONCAT_NULL_YIELDS_NULL

Definition

CONCAT_NULL_YIELDS_NULL Statement Controls whether Concatenation results are treated as NULL or Empty String values.

Syntax

SET CONCAT_NULL_YIELDS_NULL { ON | OFF } ;

Default Settings

Azure SQL DB in both SSMS and Azure Data Studio

ON – [Default] (Results NULL)
OFF – Configure ( SET CONCAT_NULL_YIELDS_NULL OFF; )

On-Premises Database in SSMS

OFF – [Default]
ON – Configure ( SET CONCAT_NULL_YIELDS_NULL ON; )

Important things

Below points are the important things that are recommended by Microsoft Documentation.

  • In future versions of SQL Server, CONCAT_NULL_YIELDS_NULL settings will always be ON.
  • Any applications that explicitly set this option to OFF will generate an error.
  • Avoid using this feature in new development work.
  • Plan to modify applications that currently use this feature.

Example

SQL Script

SET CONCAT_NULL_YIELDS_NULL ON;
 
SELECT 'ARUL' + NULL + 'MOUZHI' AS [ON];
 
SET CONCAT_NULL_YIELDS_NULL OFF;
 
SELECT 'ARUL' + NULL + 'MOUZHI' AS [OFF];

Image Pic

Result set Pic

Explanation

When SET CONCAT_NULL_YIELDS_NULL is ON,
Concatenating a null value with a string yields a NULL result.
For example, SELECT ‘ARUL’ + NULL yields NULL.

When SET CONCAT_NULL_YIELDS_NULL is OFF,
Concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
For example, SELECT ‘ARUL’ + NULL yields ARUL.

To handle our codes irrespective of CONCAT_NULL_YIELDS_NULL setting changes

If our current CONCAT_NULL_YIELDS_NULL is OFF and future version supports ON, then our code needs to be handled. We can use like below-

SQL Script

SET CONCAT_NULL_YIELDS_NULL ON;
 
SELECT 'ARUL' + NULL + 'MOUZHI' AS [ON],
'ARUL' + COALESCE(NULL,'') + 'MOUZHI' handled;
 
SET CONCAT_NULL_YIELDS_NULL OFF;
 
SELECT 'ARUL' + NULL + 'MOUZHI' AS [OFF],
'ARUL' + COALESCE(NULL,'') + 'MOUZHI' handled;

Image Pic

Result Set Pic

Notes

If SET CONCAT_NULL_YIELDS_NULL is OFF,
any CREATE, UPDATE, INSERT and DELETE statements on tables with indexes on computed columns, filtered indexes, spatial indexes or indexed views will fail.

Conclusion

Thus, we saw some key details regarding CONCAT_NULL_YIELDS_NULL like Definition, Syntax, Important things to remind, Notes and Default Settings in Azure SQL Db and On-Premises DB under SSMS and Azure Data Studio.

Article Categories:
SQL Server

Comments

  • Thanks for providing this list of SEO blogs. They are very useful while searching for new SEO strategies and techniques. Some of them I know but quite a few are new to me will check those out. Keep posting.

    sabuj hossan March 17, 2020 1:07 pm Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares