CONCAT String with NULL - Changing default behavior of SSMS

Question

We are on Sybase ASE and are in the processing migrating to MS SQL Server.

In Sybase ASE

select null + 'DBMS' ==> DBMS

In MS SQL Server (with SSMS)

In SSMS QUERY Options has by default "CONCAT_NULL_YIELDS_NULL" set to ON.

select null + 'DBMS' ==> NULL

If we override every session with "SET CONCAT_NULL_YIELDS_NULL" to OFF then we get
1. select null + 'DBMS' ==> DBMS
We are looking for changing the default behavior of SSMS. Wondering what is the right means of accomplishing this.
Though the example here is about SSMS, we want all clients to have CONCAT_NULL_YIELDS_NULL to be turned off. We do not know the default behavior of different drivers yet.

Thanks for your help!

sql-server-helper · Accepted Answer

As mentioned in https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver16

> SET CONCAT_NULL_YIELDS_NULL OFF and the CONCAT_NULL_YIELDS_NULL OFF database option are deprecated. Starting with SQL Server 2017 (14.x), CONCAT_NULL_YIELDS_NULL is always set to ON. Deprecated features shouldn't be used in new applications

And as mentioned in https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver16

> In upcoming versions of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON, and any applications that explicitly set the option to OFF will trigger an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Forum Discussion

CONCAT String with NULL - Changing default behavior of SSMS

1 Reply