Concat null yield null statement in SQL Server

The concat null yield null statement in SQL Server controls how the concatenation operator handles NULL values. This will affect on current session.

Syntex  

Set concat_null_yield_null on/off

By default is is on.

 When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result.

See the below example

SET CONCAT_NULL_YIELDS_NULL ON  select 'Bagesh' + null+ 'kumar' as Full_Name

This will return the null value.

If we set OFF then null will be ignored.

SET CONCAT_NULL_YIELDS_NULL OFF select 'Bagesh ' + null+ ' kumar' as Full_Name

It is very useful when we are concatenating multiple columns and these columns are having change to have null values.  If we are not using this setting then we need to handle the null value using ISNULL function or coalesce function. For the less columns it is good but if we have multiple column the better to use this set statement.

SET CONCAT_NULL_YIELDS_NULL must be ON when creating or altering indexed views, indexes on computed columns, filtered indexes or spatial indexes. If SET CONCAT_NULL_YIELDS_NULL is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns, filtered indexes, spatial indexes or indexed views will fail.

Be careful when we are using this setting, as it can affects the behavior of existing application and queries.