Forum Discussion
Solved
CONCAT String with NULL - Changing default behavior of SSMS
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 "CON...
- 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.
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.
> 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.