Posted in 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




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


This time the index gets created successfully.


Hope you like the post.


Kapil Singh

Follow me on Twitter



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s