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
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.