Fix QUOTED_IDENTIFIER (Transact-SQL)

  • Article
  • 5 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Example yesAzure Synapse Analytics yesAnalytics Platform Organisation (PDW)

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 comprise characters not generally allowed past the Transact-SQL syntax rules for identifiers.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server, Azure SQL Database and serverless SQL pool in Azure Synapse Analytics  Prepare QUOTED_IDENTIFIER { ON | OFF }                          
              -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  SET QUOTED_IDENTIFIER ON

Remarks

When Ready QUOTED_IDENTIFIER is ON (default), identifiers tin can be delimited by double quotation marks (" "), and literals must be delimited by single quotation marks (' '). All strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers exercise not accept to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not mostly allowed in Transact-SQL identifiers. Double quotation marks cannot be used to circumscribe literal string expressions; unmarried quotation marks must exist used to enclose literal strings. If a unmarried quotation mark (') is office of the literal string, it can be represented by 2 single quotation marks (''). SET QUOTED_IDENTIFIER must exist ON when reserved keywords are used for object names in the database.

When Prepare QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more than information, run into Database Identifiers. Literals can be delimited by either single or double quotation marks. If a literal string is delimited past double quotation marks, the cord can contain embedded unmarried quotation marks, such as apostrophes.

Annotation

QUOTED_IDENTIFIER does not bear on delimited identifiers enclosed in brackets ([ ]).

Ready QUOTED_IDENTIFIER must be ON when yous are creating or irresolute indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, and so CREATE, UPDATE, INSERT, and DELETE statements volition fail on tables with indexes on computed columns, or tables with indexed views. For more information about required Fix option settings with indexed views and indexes on computed columns, see Considerations when you use the SET statements.

Set QUOTED_IDENTIFIER must be ON when yous are creating a filtered index.

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

The SQL Server Native Client ODBC commuter and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can exist configured in ODBC information sources, in ODBC connection attributes, or OLE DB connexion backdrop. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored equally ON in the table's metadata fifty-fifty if the option is set to OFF when the table is created.

When a stored process is created, the SET QUOTED_IDENTIFIER and Set up ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored process, the setting of Set QUOTED_IDENTIFIER is not changed.

When Set up ANSI_DEFAULTS is ON, QUOTED_IDENTIFIER is also ON.

Fix QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of Alter DATABASE.

SET QUOTED_IDENTIFIER takes upshot at Transact-SQL parse-time and only affects parsing, not query optimization or query execution.

For a superlative-level ad-hoc batch parsing begins using the session's electric current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER volition change the parsing behavior from that point on, and save that setting for the session. And so later the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be ready according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

Static Transact-SQL in a stored procedure is parsed using the QUOTED_IDENTIFIER setting in effect for the batch that created or altered the stored procedure. SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static Transact-SQL.

For a nested batch using sp_executesql or exec(), the parsing begins using the QUOTED_IDENTIFIER setting of the session. If the nested batch is inside a stored procedure, parsing starts using the QUOTED_IDENTIFIER setting of the stored procedure. As the nested batch is parsed, any occurrence of SET QUOTED_IDENTIFIER will modify the parsing behavior from that point on, but the session'south QUOTED_IDENTIFIER setting will non exist updated.

To view the current setting for this setting, run the following query:

              DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 )  Brainstorm     SET @QUOTED_IDENTIFIER = 'ON'; Terminate  SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

Permissions

Requires membership in the PUBLIC role.

Examples

A. Using the quoted identifier setting and reserved discussion object names

The post-obit case shows that the Set up QUOTED_IDENTIFIER setting must exist ON, and the keywords in table names must be in double quotation marks to create and employ objects that have reserved keyword names.

              SET QUOTED_IDENTIFIER OFF GO  -- Create argument fails. CREATE TABLE "select" ("identity" INT IDENTITY Non NULL, "order" INT NOT NULL); GO  Fix QUOTED_IDENTIFIER ON; Get  -- Create statement succeeds. CREATE TABLE "select" ("identity" INT IDENTITY Not Zip, "order" INT Non Nil); Get  SELECT "identity","order" FROM "select" Society BY "order"; Become  DROP TABLE "SELECT"; Get  SET QUOTED_IDENTIFIER OFF; GO

B. Using the quoted identifier setting with single and double quotation marks

The post-obit example shows the way single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

              Gear up QUOTED_IDENTIFIER OFF; GO  Utilise AdventureWorks2012; IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES     WHERE TABLE_NAME = 'Test')     DROP Tabular array dbo.Test; GO Utilise AdventureWorks2012; CREATE Table dbo.Test (ID INT, String VARCHAR(xxx)) ; Become  -- Literal strings tin be in single or double quotation marks. INSERT INTO dbo.Exam VALUES (1, "'Text in unmarried quotes'"); INSERT INTO dbo.Test VALUES (two, '''Text in single quotes'''); INSERT INTO dbo.Exam VALUES (3, 'Text with 2 '''' unmarried quotes'); INSERT INTO dbo.Test VALUES (4, '"Text in double quotes"'); INSERT INTO dbo.Examination VALUES (5, """Text in double quotes"""); INSERT INTO dbo.Exam VALUES (6, "Text with 2 """" double quotes"); Get  Prepare QUOTED_IDENTIFIER ON; Become  -- Strings inside double quotation marks are now treated -- as object names, and then they cannot be used for literals. INSERT INTO dbo."Test" VALUES (vii, 'Text with a single '' quote'); GO  -- Object identifiers do not accept to be in double quotation marks -- if they are not reserved keywords. SELECT ID, Cord FROM dbo.Test; GO  DROP Table dbo.Test; GO  Set up QUOTED_IDENTIFIER OFF; Go

Here is the result set.

                              ID          String  ----------- ------------------------------  one           'Text in single quotes'  2           'Text in single quotes'  3           Text with 2 '' single quotes  4           "Text in double quotes"  5           "Text in double quotes"  6           Text with 2 "" double quotes  7           Text with a single ' quote

Come across Also

CREATE DATABASE
CREATE DEFAULT
CREATE Process
CREATE RULE
CREATE Table
CREATE TRIGGER
CREATE VIEW
Data Types
EXECUTE
SELECT
SET Statements
SET ANSI_DEFAULTS
sp_rename
Database Identifiers