SET Concat_null_yields_null - TSQL Tutorial

SET Concat_null_yields_null - Checks whether concatenation results are treated as null or empty string values.

SET Concat_null_yields_null Syntax:

SET CONCAT_NULL_YIELDS_NULL { ON | OFF } ;

SET Concat_null_yields_null Example:

USE model;
GO
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'test' + NULL;
GO

Results
NULL

USE model;
GO
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'test' + NULL;
GO

Results
test