BOL: However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.

The right answer is: it depends. 😀