Enabling quoted identifier for SQL Server

In the past, SQL Server allowed interchangeable use of quotation marks and apostrophes for strings. However, ANSI introduced new guidelines stating that apostrophes should be used for strings, while quotation marks should be used for funky names. Square brackets are not a concern. Therefore, to quote a funky column or table name, you must use quotation marks, as they were already being used to represent strings in SQL Server.

This applies to various platforms including SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and Analytics Platform System (PDW).

Enforcing the ISO guidelines pertaining to the use of quotation marks in delimiting SQL Server identifiers and literal strings results in various outcomes. Identifiers surrounded by double quotation marks may either include Transact-SQL reserved keywords or feature symbols that are not typically allowed as per the rules governing Transact-SQL syntax for identifiers.

The conventions of Transact-SQL syntax are presented below:
Topic link icon

Syntax

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

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
SET QUOTED_IDENTIFIER ON

Note

For documentation on versions of SQL Server preceding 2014, refer to the Previous versions documentation to access Transact-SQL syntax.

Remarks

Enabling

SET QUOTED_IDENTIFIER

allows for identifiers to be enclosed in double quotation marks (” “), and literals to be enclosed in
single quotation marks
(‘ ‘). Quoted identifiers are interpreted as object identifiers, and do not need to abide by Transact-SQL identifier rules, including the allowance of reserved keywords and special characters. However, double quotation marks cannot be used to enclose literal string expressions, and single quotation marks must be used instead. If a single quotation mark appears within the literal string, it can be represented by two single quotation marks (”). Enabling

SET QUOTED_IDENTIFIER

is necessary when using reserved keywords for object names in the database.

If

SET QUOTED_IDENTIFIER

is set to OFF, then identifiers must follow Transact-SQL rules and cannot be quoted. For further details on Database Identifiers, refer to the relevant documentation. When it comes to literals, they can be enclosed in either single or double quotation marks. Double quotation marks can be used to delimit literal strings that contain apostrophes.

Note

The use of QUOTED_IDENTIFIER has no impact on identifiers that are enclosed in brackets ([ ]).

Enabling

SET QUOTED_IDENTIFIER

is essential for modifying or generating Indexes on Computed Columns or indexed views. Disabling

SET QUOTED_IDENTIFIER

will result in the failure of CREATE, UPDATE, INSERT, and DELETE statements on tables that have indexes on computed columns or indexed views. To learn more about necessary SET option configurations for indexed views and indexes on computed columns, refer to the considerations outlined in the SET statements.

Ensure that the filtered index creation has the

SET QUOTED_IDENTIFIER

setting turned on.

Xml Data Type Methods necessitate the activation of

SET QUOTED_IDENTIFIER

.

Upon establishing a connection, the SQL Server Native Client ODBC driver and sql server native client ole db Provider for SQL Server automatically activate QUOTED_IDENTIFIER to ON. This setting can be modified through ODBC data sources, ODBC connection attributes, or OLE DB connection properties. For connections originating from DB-Library applications, SET QUOTED_IDENTIFIER defaults to OFF.

The metadata of a table always stores the QUOTED IDENTIFIER option as ON, regardless of whether it was set to OFF during the table’s creation.

Upon creating a stored procedure, the captured SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are utilized for later executions of said procedure.

The value of SET QUOTED_IDENTIFIER remains unaffected when used in a stored procedure.

If

SET ANSI_DEFAULTS

is activated, then QUOTED_IDENTIFIER will also be turned on.

The ALTER DATABASE’s QUOTED_IDENTIFIER setting is also aligned with

SET QUOTED_IDENTIFIER

.

The impact of

SET QUOTED_IDENTIFIER

is limited to Transact-SQL parse-time and does not extend to query optimization or execution.

At the start of a high-level ad-hoc batch parsing, the current QUOTED_IDENTIFIER setting of the session is used. Parsing behavior is altered from that point forward any time

SET QUOTED_IDENTIFIER

appears in the batch, and this modified setting is saved for the session. Upon completion of the batch parsing and execution, the QUOTED_IDENTIFIER setting of the session is determined by the final occurrence of

SET QUOTED_IDENTIFIER

in the batch.

When parsing static Transact-SQL in a stored procedure, the QUOTED_IDENTIFIER setting used in creating or modifying the procedure is taken into account. It should be noted that using

SET QUOTED_IDENTIFIER

as static Transact-SQL within the body of a stored procedure does not change this behavior.

When processing a nested batch containing either

sp_executesql

or

exec()

, the QUOTED_IDENTIFIER setting of the session is used for parsing. If the nested batch is within a stored procedure, the QUOTED_IDENTIFIER setting of the stored procedure is used instead. During the parsing of the nested batch, parsing behavior changes from the point where

SET QUOTED_IDENTIFIER

is encountered, but the session’s QUOTED_IDENTIFIER setting remains unchanged.

To check the present configuration of this setting, execute the subsequent query.

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

Permissions

Membership to the

PUBLIC

role is mandatory.

Examples

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

The below illustration demonstrates the requirement for utilizing the

SET QUOTED_IDENTIFIER

setting in conjunction with enclosing the keywords in table names with double quotation marks. This is necessary for the creation and use of objects that have 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. Using the quoted identifier setting with single and double quotation marks

The utilization of single and double quotation marks in string expressions is demonstrated in the upcoming example, where

SET QUOTED_IDENTIFIER

is assigned values of

ON

and

OFF

.

SET QUOTED_IDENTIFIER OFF;
GO
USE AdventureWorks2012;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'Test')
    DROP TABLE dbo.Test;
GO
USE AdventureWorks2012;
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

Here is the result set.

 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

The following commands are available:
– CREATE DATABASE
– CREATE DEFAULT
– CREATE PROCEDURE
– CREATE RULE
– CREATE TABLE
– CREATE TRIGGER
– CREATE VIEW
Other features include:
– Data Types
– EXECUTE
– SELECT
– SET Statements
– SET ANSI_DEFAULTS
– sp_rename
– Database Identifiers

Frequently Asked Questions

Posted in Sql