What does `QUOTED_IDENTIFIERS` do, exactly?

by Evan Carroll   Last Updated January 13, 2018 21:06 PM

There is a really obnoxious feature in SQL Server called QUOTED_IDENTIFIERS and I wanted to know more about it. In short, with the option set to ON, which is not the default with the Linux client, everything works. Without it, some things magically fail. Microsoft claims the option is the DEFAULT, and then they default-change it in their own default-client. With SET QUOTED_IDENTIFIERS OFF;, this works,

CREATE TABLE f (
  a int,
  b int,
  g AS (CASE WHEN a>b THEN a ELSE b END),
  l AS (CASE WHEN a>b THEN b ELSE a END),
  UNIQUE(a,b)
);

With SET QUOTED_IDENTIFIERS OFF;, this does not work,

CREATE TABLE f (
  a int,
  b int,
  g AS (CASE WHEN a>b THEN a ELSE b END),
  l AS (CASE WHEN a>b THEN b ELSE a END),
  UNIQUE(g,l)
);

I just want to know how g and l are more of an identifier than a and b, and how not supporting this syntax except when enabled by the client was a good or sensible idea? What exactly is this option doing?

I only ask because twice now I've been bitten by this,

Tags : sql-server


Related Questions


Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM