SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
set CONCAT_NULL_YIELDS_NULL   on;
select 'x' + cast(null as varchar)

(No column name)

null

set CONCAT_NULL_YIELDS_NULL   off;
select 'x' + cast(null as varchar)

(No column name)

x

set CONCAT_NULL_YIELDS_NULL   off;
if (1=0)
begin
    set CONCAT_NULL_YIELDS_NULL   on;
end
select 'x' + cast(null as varchar)

(No column name)

x

set CONCAT_NULL_YIELDS_NULL   on;
if (1=0)
begin
    set CONCAT_NULL_YIELDS_NULL   off;
end
select 'x' + cast(null as varchar)

(No column name)

null

SELECT @@OPTIONS AS OriginalOptionsValue;  
SET CONCAT_NULL_YIELDS_NULL OFF;  
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;  
  
SET CONCAT_NULL_YIELDS_NULL ON;  
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON; 

OriginalOptionsValue

5496

ResultWhen_OFF
OptionsValueWhen_OFF

abc
1400

ResultWhen_ON
OptionsValueWhen_ON

null
5496

SELECT @@OPTIONS AS OriginalOptionsValue;  
SET QUOTED_IDENTIFIER OFF;  
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;  
  
SET QUOTED_IDENTIFIER ON;  
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON; 

OriginalOptionsValue

5496

ResultWhen_OFF
OptionsValueWhen_OFF

null
5496

ResultWhen_ON
OptionsValueWhen_ON

null
5496