SET CONCAT_NULL_YIELDS_NULL

Fourth post of SET Statement series.

When concantating multiple columns you always need to verify whether your columns have null values. If they have null values when you concantating them, final result also will become NULL as shown below.

SELECT 'dbfriend' + NULL ;
GO

By default output of this is NULL. However, you can change this behavior by setting the CONCAT_NUL_YIELDS_NULL to OFF (default setting is ON)

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'dbfriend' + NULL ;
GO

Now the output of the above query is dbfriend not NULL.

Let us check this when you are concantating table columns.

USE AdventureWorks2012
GO

SELECT title + '' +
    firstname + '' +
    MiddleName + '' +
    lastname Full_Name
      FROM Person.Person

Result for the above query where you can see lot of null values as either of one column has a null values final result will be null.

Let us run this with the setting enable.

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
USE AdventureWorks2012
GO

SELECT title + '' +
    firstname + '' +
    MiddleName + '' +
    lastname Full_Name
      FROM Person.Person

And the result is shown below and null values are gone!

With SQL Server 2012 we have new feature called CONCAT to support this. So in SQL Server 2012, you don’t need to set this setting.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY