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:

Now lets CONCAT_NULL_YIELDS_NULL set to OFF.

Create an index on computed column FullName:

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

img_Concat1

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

This time the index gets created successfully.

img_Concat2

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