4

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.

0
0

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.

3
  • Could be wrong, but I don't think "nondeterministic" is relevant, that just means that it can return different results depending on what's going on with the connection/server etc. The result you see in the final batch is perfectly explained when you think of QUOTED_IDENTIFIER as a parse-time option, it doesn't change the currently running setting until you finish the batch Apr 29 at 11:59
  • @Charlieface nice input. But since the result can be different, how can he trust that the if ((256 & @@options) = 256) condition was properly evaluated and use the result to decide whether QUOTED_IDENTIFIER is ON or OFF?
    – Ronaldo
    Apr 29 at 12:15
  • 2
    It's telling you something slightly different than what you think. It doesn't tell you whether QUOTED_IDENTIFIER is on/off in the parse stage, it only tells you the current default on the connection (what setting would be in force if you execute a new batch without any SET). I don't think there is any way of showing the current parse-time setting, other than doing the obvious and looking for SET statements (remember they ignore IF blocks). Apr 29 at 12:43
7
+250

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
3
  • I removed the explanation from my answer since yours seems to be more accurate, but I still don't get it. How come in this Demonstration of how changes affect behavior it captures the changes of QUOTED_IDENTIFIER on the same batch without the need of isolating them with a GO?
    – Ronaldo
    May 3 at 11:01
  • CONCAT_NULL_YIELDS_NULL appears to work at execution time see dbfiddle.uk/…, whereas QUOTED_IDENTIFIER does not May 3 at 11:41
  • Woooooow! now I see the mess I was doing. I hadn't noticed the sample code on the @@OPTION doc was setting CONCAT_NULL_YIELDS_NULL instead of QUOTED_IDENTIFIER. Thanks.
    – Ronaldo
    May 3 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.