How do we know whether CONCAT_NULL_YIELDS_NULL is ON or OFF ?

At session/database level, we can get know the value of CONCAT_NULL_YIELDS_NULL by using SESSIONPROPERTY or DATABASEPROPERTYEX.

If the query returns 1 that means when any value is concated with NULL it will return NULL. If the query returns 0 it means when any value is concated with NULL, it will still return original value and impact of NULL will be ignored

SELECT SESSIONPROPERTY  ('CONCAT_NULL_YIELDS_NULL') SessionLevel
GO
SELECT DATABASEPROPERTYEX ('master', 'IsNullConcat') DatabaseLevel