Uncategorized

How CONCAT_NULL_YIELDS_NULL behave with 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 while creating index 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:

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

SET CONCAT_NULL_YIELDS_NULL ON

This time the index gets created successfully.

img_Concat2

Hope you like the post.

Regards,

Kapil Singh

Follow me on Twitter

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s