SET CONCAT_NULL_YIELDS_NULL
Controls whether or not concatenation results are treated as null or empty string values.
Syntax
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
Remarks
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.
If not specified, the setting of the concat null yields null database option applies.
Note SET CONCAT_NULL_YIELDS_NULL is the same setting as the concat null yields null setting of sp_dboption.
The setting of SET CONCAT_NULL_YIELDS_NULL is set at execute or run time and not at parse time.
SET CONCAT_NULL_YIELDS_NULL must be ON when creating or manipulating indexes on computed columns or indexed views. If SET CONCAT_NULL_YIELDS_NULL is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.