SET CONCAT_NULL_YIELDS_NULL : This set statement is used to configure the current environment or session to return NULL value while The NULL is concatenated with other values in an expression. This set setting will be ON by default in the future version of SQL Server and it cannot be set to OFF. So, we have to handle this set statement carefully in the current and future application design.
The best practice is to keep the SET CONCAT_NULL_YIELDS_NULL to ON throughout the data processing.
Usage
SET CONCAT_NULL_YIELDS_NULL ON
or
SET CONCAT_NULL_YIELDS_NULL OFF
Example
1. A simple select statement that concatenates a string with NULL value.
When CONCAT_NULL_YIELDS_NULL is OFF, it returns the string value.
When CONCAT_NULL_YIELDS_NULL is ON, it returns NULL value.
I hope that you have understood the use of this SET statement. Happy coding.
SQL Server Rider 