Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

-- Default is ON
SET QUOTED_IDENTIFIER ON

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Database Identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark () is part of the literal string, it can be represented by two single quotation marks (). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see “Considerations When You Use the SET Statements” in SET Statements.

SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Last modified: December 20, 2019

Author

Comments

Write a Reply or Comment