Skip to content

set-quoted-identifier-transact-sql.md

Latest commit

 

History

History
244 lines (181 loc) · 10.5 KB

set-quoted-identifier-transact-sql.md

File metadata and controls

244 lines (181 loc) · 10.5 KB
title SET QUOTED_IDENTIFIER (Transact-SQL)
description SET QUOTED_IDENTIFIER causes the Database Engine to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer randolphwest
ms.date 04/08/2026
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
QUOTED_IDENTIFIER_TSQL
SET_QUOTED_IDENTIFIER_TSQL
SET QUOTED_IDENTIFIER
QUOTED_IDENTIFIER
helpviewer_keywords
delimited identifiers [SQL Server]
identifiers [SQL Server], delimited
QUOTED_IDENTIFIER option
quoted identifiers
ISO delimited identifiers rules
SET QUOTED_IDENTIFIER statement
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

SET QUOTED_IDENTIFIER (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

The QUOTED_IDENTIFIER setting causes [!INCLUDE ssNoVersion] to follow the ISO rules for quotation mark delimiting identifiers and literal strings. If you use double quotation marks to delimit identifiers, you can use either [!INCLUDE tsql] reserved keywords, or include characters that the [!INCLUDE tsql] syntax rules usually don't allow in identifiers.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

Syntax for SQL Server, Azure SQL Database, serverless SQL pool in Azure Synapse Analytics, and Microsoft Fabric.

SET QUOTED_IDENTIFIER { ON | OFF }

Syntax for Azure Synapse Analytics and Parallel Data Warehouse.

SET QUOTED_IDENTIFIER ON

Remarks

When you set SET QUOTED_IDENTIFIER to ON (the default), you can use double quotation marks ("") to delimit identifiers, and you must use single quotation marks ('') to delimit literals. All strings enclosed in double quotation marks are interpreted as object identifiers. Quoted identifiers don't need to follow the [!INCLUDE tsql] rules for identifiers. They can be keywords and can include characters that aren't allowed in [!INCLUDE tsql] identifiers. If a double quotation mark (") is part of the identifier, you can represent it by two double quotation marks (""). You must set SET QUOTED_IDENTIFIER to ON when you use reserved keywords for object names in the database.

When you set SET QUOTED_IDENTIFIER to OFF, you can't quote identifiers and they must follow all [!INCLUDE tsql] rules for identifiers. For more information, see Database identifiers. You can delimit literals by either single or double quotation marks. If you delimit a literal string by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

Note

QUOTED_IDENTIFIER doesn't affect delimited identifiers enclosed in brackets ([ and ]).

You must set SET QUOTED_IDENTIFIER to ON when you create or change indexes on computed columns or indexed views. If you set SET QUOTED_IDENTIFIER to OFF, CREATE, UPDATE, INSERT, and DELETE statements fail on tables with indexes on computed columns, or tables with indexed views. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations when you use the SET statements.

You must set SET QUOTED_IDENTIFIER to ON when you create a filtered index.

You must set SET QUOTED_IDENTIFIER to ON when you invoke xml data type methods.

The [!INCLUDE ssNoVersion] Native Client ODBC driver and [!INCLUDE ssNoVersion] Native Client OLE DB Provider for [!INCLUDE ssNoVersion] automatically set QUOTED_IDENTIFIER to ON when connecting. You can configure this setting in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

When you create a table, the table's metadata always stores the QUOTED_IDENTIFIER option as ON even if you set the option to OFF when creating the table.

When you create a stored procedure, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When you execute SET QUOTED_IDENTIFIER inside a stored procedure, the setting doesn't change.

When you set SET ANSI_DEFAULTS to ON, QUOTED_IDENTIFIER is also set to ON.

SET QUOTED_IDENTIFIER corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE.

SET QUOTED_IDENTIFIER takes effect at [!INCLUDE tsql] parse-time and only affects parsing, not query optimization or query execution.

For a top-level ad hoc batch, parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed, any occurrence of SET QUOTED_IDENTIFIER changes the parsing behavior from that point on, and saves that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFIER setting is set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

Static [!INCLUDE tsql] 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 [!INCLUDE tsql].

For a nested batch that uses sp_executesql or exec(), 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 changes the parsing behavior from that point on, but the session's QUOTED_IDENTIFIER setting isn't updated.

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

DECLARE @QUOTED_IDENTIFIER AS VARCHAR (3) = 'OFF';

IF ((256 & @@OPTIONS) = 256)
BEGIN
    SET @QUOTED_IDENTIFIER = 'ON';
END

SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

Permissions

Requires membership in the public fixed database role.

Examples

A. Use the quoted identifier setting and reserved word object names

The following example shows that the SET QUOTED_IDENTIFIER setting must be ON, and the keywords in table names must be in double quotation marks to create and use objects with reserved keyword names.

SET QUOTED_IDENTIFIER OFF;
GO

-- Create statement fails.
CREATE TABLE "select"
(
    "identity" INT IDENTITY NOT NULL,
    "order" INT NOT NULL
);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Create statement succeeds.
CREATE TABLE "select"
(
    "identity" INT IDENTITY NOT NULL,
    "order" INT NOT NULL
);
GO

SELECT "identity",
       "order"
FROM "select"
ORDER BY "order";
GO

DROP TABLE "SELECT";
GO

SET QUOTED_IDENTIFIER OFF;
GO

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

The following example shows how single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

SET QUOTED_IDENTIFIER OFF;
GO

USE AdventureWorks2025;
GO

IF EXISTS (SELECT TABLE_NAME
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'Test')
    DROP TABLE dbo.Test;
GO

USE AdventureWorks2025;
GO

CREATE TABLE dbo.Test
(
    ID INT,
    String VARCHAR (30)
);
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 single quotes''');

INSERT INTO dbo.Test
VALUES (3, 'Text with 2 '''' single quotes');

INSERT INTO dbo.Test
VALUES (4, '"Text in double quotes"');

INSERT INTO dbo.Test
VALUES (5, """Text in double quotes""");

INSERT INTO dbo.Test
VALUES (6, "Text with 2 """" double quotes");
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Strings inside double quotation marks are now treated
-- as object names, so they cannot be used for literals.
INSERT INTO dbo."Test"
VALUES (7, 'Text with a single '' quote');
GO

-- Object identifiers do not have to be in double quotation marks
-- if they are not reserved keywords.
SELECT ID,
       String
FROM dbo.Test;
GO

DROP TABLE dbo.Test;
GO

SET QUOTED_IDENTIFIER OFF;
GO

[!INCLUDE ssResult]

 ID          String
 ----------- ------------------------------
 1           '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

Related content