SQL Server provides various settings that can affect the behavior of certain operations. One such setting is CONCAT_NULL_YIELDS_NULL, which determines how NULL values are handled when concatenated with other values.

In this blog post, we will explore how to check the CONCAT_NULL_YIELDS_NULL setting at both the database and session levels.

Checking the Database Level Setting

To determine if the CONCAT_NULL_YIELDS_NULL setting is enabled at the database level, you can use the following script:

SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsNullConcat');

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

Checking the Session Level Setting

To identify the CONCAT_NULL_YIELDS_NULL setting at the session level, you can run the following script:

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL');

Similar to the database level setting, if the query returns 1, it means that when any value is concatenated with NULL, it will return NULL. If the query returns 0, it means that when any value is concatenated with NULL, it will still return the original value and ignore the impact of NULL.

It’s important to note that the session level setting can override the database level setting. So even if the database level setting is enabled, if the session level setting is disabled, the behavior will follow the session level setting.

Additionally, it’s worth mentioning that there are alternative functions like COALESCE that can be used to achieve similar results as the ISNULL function, as demonstrated by Manigandan in the comments of the previous blog post.

Understanding and being aware of these settings can help you ensure the desired behavior when working with NULL values in your SQL Server queries.