SQL Server: set quoted_identifier

Interpretation of quotation marks

set quoted_identifier on
------------------------

--
--  With quoted_identifier on, text that is enclosed in
--  quotation marks is interpreted as identifiers. In the
--  following statement, these identifiers are the name
--  of the table being craeted and its column names
--
    create table "quoted identifier test" (
      "col one"  varchar(10),
      "col two"  varchar(10)
    );

--  Because quotes indicate an identifer rather than a
--  string, the following print statement would cause:
--      Msg 128: The name "hello" is not permitted in this context.
--
--  print("hello 1");

--
--  With quoted identifiers, strings must be enclosed in single quotes:
--
    print('hello 2');

    insert into "quoted identifier test" values ( 'one' , 'foo' );

--
--  It is always possible to
    insert into [quoted identifier test] values ( 'two' , 'bar' );

--
--  Strings can never be enclosed in square brackets, regardless of the
--  setting of quoted_identifier. The following print statement would
--  cause the error message
--      Msg 128, Level 15, State 1,
--      The name "hello 3" is not permitted in this context....
--
--  print([hello 3]);

set quoted_identifier off
-------------------------

--
--  If quoted_identifier is set to off, text that is enclosed in quotation
--  marks is interpreted as strings.
--  Therefore, in the following statement, "quoted identifier test" is
--  taken to be a string and would cause the error message:
--      Msg 102, Level 15, State 1...
--      Incorrect syntax near 'quoted identifier test'.
--
--  insert into "quoted identifier test" values ( 'two' , 'bar' );
--

--
--  In order to refer to an identifier with spaces etc., the identifier
--  must be enclosed in square brackets.
--
    insert into [quoted identifier test] values ("three", "baz");
    print("hello 4");

--  Never works:
--  print([hello 5]);

    drop table [quoted identifier test];

Get current value of quoted_identifier

select case when @@options & power(2, 8) > 0 then 'yes' else 'no' end;

Value of quoted_identifier not retained across SQL batches

    set nocount on

    set quoted_identifier off
    select '1st batch, quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;

go

    select '2nd batch, quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;
    set quoted_identifier on

go

    select '3rd batch, quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;
--    
-- 1st batch, quoted_identifier is off
-- 2nd batch, quoted_identifier is on
-- 3rd batch, quoted_identifier is on    

Value determined at parse time of SQL batch

set nocount on

set quoted_identifier off

    select 'quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;

set quoted_identifier on

    select 'quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;

set quoted_identifier off

    select 'quoted_identifier is ' + case when @@options & power(2, 8) > 0 then 'on' else 'off' end;

go

-- quoted_identifier is off
-- quoted_identifier is off
-- quoted_identifier is off

Filtered index

create table tq84_qi (
   id    integer,
   val   varchar(3)
);

insert into tq84_qi
values (1, 'foo'),
       (2, 'bar'),
       (3,  null),
       (4, 'baz'),
       (5,  null);	

go

   set quoted_identifier off 
   create unique index tq84_qi_ix on tq84_qi (val) where val is not null;
--
-- create index statement causes:
--    Msg 1934, Level 16, State 1...
--    CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
--

go

   set quoted_identifier on
   create unique index tq84_qi_ix on tq84_qi (val) where val is not null;

go

drop table tq84_qi

Current value of quoted_identifier stored with stored procedure when creating it

select
   uses_quoted_identifier
from
   sys.sql_modules
where
   object_id = …

See also