Multiple Set Quoted_identifier within one script takes the last one?

I'm using SQL Server 2019 and just found a weird behaviour.
Research has not gotten me anywhere further.

Can someone please explain this behaviour?

SET QUOTED_IDENTIFIER ON; 
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY 
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY 
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    -- SET QUOTED_IDENTIFIER OFF
    -- if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:

1- quoted_identifier is on
2- quoted_identifier is on

but the following code:

SET QUOTED_IDENTIFIER ON; 
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY 
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY 
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    SET QUOTED_IDENTIFIER OFF
    if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:

1- quoted_identifier is off
2- quoted_identifier is off

Even though it isn't going into the catch!!!
I must be missing something.

I was even able to simplify the code to the most simple :

SET QUOTED_IDENTIFIER ON; 
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
SET QUOTED_IDENTIFIER OFF
if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';

Results:

1- quoted_identifier is off
2- quoted_identifier is off

I have some code that uses FOR XML, which requires me to set quoted Identifier ON, but I need to p

This is by design:

For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

The behaviour of QUOTED_IDENTIFIER is therefore dependent on the parsing of the batch, not the execution. The default at the start of parsing the batch comes from the current connection settings.

@@OPTIONS only shows you the current default, that would be used if a batch was parsed. This is why SSMS always puts SET in a separate batch.

The execution of SET is not relevant to the behaviour of @@OPTIONS, except to change later batches' default.

You can see this in action in a db-fiddle
set QUOTED_IDENTIFIER on;
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER off;
create table "select"(i int);
drop table "select";
-- Incorrect syntax near 'select'.
GO

set QUOTED_IDENTIFIER off;
if (1=0)
begin
set QUOTED_IDENTIFIER on;
end
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER on;
if (1=0)
begin
set QUOTED_IDENTIFIER off;
end
create table "select"(i int);
drop table "select";
-- Incorrect syntax near 'select'.
GO


StackExchange Database Administrators Q#290663, answer score: 11