Multiple Set Quoted_identifier within one script takes the last one?

Question

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 put it back to OFF, no matter if the XML portion succeeds or fails. How would you do that?

My tests show that if I put the SET QUOTED_IDENTIFIER to off in the CATCH, the insert fails to say that my quoted identifier is not set properly although it is set at the beginning of the TRY to ON.

Answer 1

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

Answer 2

Charlieface's answer seems to have explained appropriately the reason for the behavior you described when using SET QUOTED_IDENTIFIER, so I'll just leave a method you could use if you needed to use it inside a SP:

Suggested Solution

Isolate part of your batch on another procedure and call it from the one where the QUOTED_IDENTIFIER needs to be OFF like this: db<>fiddle - run on your computer on 2 different sessions for I couldn't make it run properly on dbfiddle.