Effect Of CONCAT_NULL_YIELDS_NULL For Computed Columns In Sql Server

Hi friends, in this blog I am going to tell you about effect of CONCAT_NULL_YIELDS_NULL for computed columns in Sql Server.

What this function does is when it set to ON it will result in NULL values when any value using + operator is NULL, while when CONCAT_NULL_YIELDS_NULL set to OFF it will return original value instead of NULL.

Note: As per the Microsoft document CONCAT_NULL_YIELDS_NULL should always be set to ON as value of CONCAT_NULL_YIELDS_NULL to OFF is not going to be supported in later versions

By default, CONCAT_NULL_YIELDS_NULL is always ON.

First we will create a table:

CREATE TABLE PersonalDetails
(
	PersonalDetailsId INT,
	FirstName NVARCHAR(20),
	MiddleName NVARCHAR(20),
	LastName NVARCHAR(20),
	FullName AS FirstName + LastName
)

Now lets CONCAT_NULL_YIELDS_NULL set to OFF.

SET CONCAT_NULL_YIELDS_NULL OFF

Create an index on computed column FullName:

CREATE INDEX ix_fullname ON PersonalDetails(FullName)

It will throw an error saying that index cannot be created on computed column when CONCAT_NULL_YIELDS_NULL is set to OFF:

So, CONCAT_NULL_YIELDS_NULL should always be ON when we create index on computed columns.

Now, let’s turn back to the default setting of CONCAT_NULL_YIELDS_NULL to ON

SET CONCAT_NULL_YIELDS_NULL ON

This time the index gets created successfully.

Hope you like the post.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter