When SET QUOTED_IDENTIFIER ON, identifiers can be delimited by double quotation ” marks, and literals must be delimited by single quotation ‘ marks. However, when SET QUOTED_IDENTIFIER OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Hmm… So what this mean?

For example,

SET QUOTED_IDENTIFIER OFF
GO

– You cannot use reserved keyword, it will fail
CREATE TABLE “select” (”identity” INT IDENTITY NOT NULL, “order” INT NOT NULL)
GO

SET QUOTED_IDENTIFIER ON
GO

– When is ON, you can use reserved keyword. It will pass.
CREATE TABLE “select” (”identity” INT IDENTITY NOT NULL, “order” INT NOT NULL)
GO

Another example,

SET QUOTED_IDENTIFIER OFF
GO

– Literal strings can be in single or double quotation marks.
INSERT INTO dbo.Test VALUES (1, “‘Text in single quotes’”)
INSERT INTO dbo.Test VALUES (2, ‘”Text in double quotes”‘)

Tags: Transact-SQL