Identifiers separated by double quotation marks

Set quoted_identifier
Enables Microsoft SQL server to comply with the SQL-92 rules for the quotation mark separator identifier and text string. The identifiers separated by double quotation marks can be keywords reserved by Transact-SQL, or can contain characters that are not allowed by the syntax rules of transact-SQL identifiers.

Syntax
Set quoted_identifier {on | off}

Note
When set quoted_identifier is on, the identifiers can be separated by double quotation marks, and the text must be separated by single quotation marks. When set quoted_identifier is off, the identifier cannot be enclosed in quotation marks and must comply with all the rules of the transact-SQL identifier. For more information, see use identifiers. The text can be separated by single or double quotation marks.

When set quoted_identifier is on, all strings separated by double quotation marks are interpreted as object identifiers. Therefore, the quoted identifiers do not have to comply with the rules of transact-SQL identifiers. They can be reserved keywords and contain characters that are generally not allowed in the transact-SQL identifier. Character string expressions cannot be separated by double quotation marks, but must be enclosed by single quotation marks. If single quotes (') are part of a text string, they can be expressed by two single quotes. When reserved keywords are used for object names in the database, set quoted_identifier must be on.

When set quoted_identifier is off (default), the text strings in the expression can be separated by single quotes or double quotation marks. If the text string is separated by double quotation marks, it can contain embedded single quotation marks, such as ellipsis.

When you create or operate an index on a calculated column or index view, set quoted_identifier must be on. If set quoted_identifier is off, the CREATE, update, insert, and delete statements on the table with indexes on the calculation column or index view will fail. For more information about the set option settings required for the index view and index on the calculation column, see "considerations when using the set statement" in set ".

During the connection, the SQL Server ODBC driver and the Microsoft ole db provider for SQL Server automatically set quoted_identifier to on. This can be configured in the ODBC data source, ODBC connection feature, or ole db connection attribute. Set quoted_identifier to off for connections from DB-Library applications.

When a stored procedure is created, the set quoted_identifier and set ansi_nulls settings are captured for subsequent calls to the stored procedure.

When set quoted_identifier is executed in the stored procedure, its settings are not changed.

Set quoted_identifier is enabled when set ansi_defaults is on.

Set quoted_identifier also corresponds to the quoted identifier setting of sp_dboption. If set quoted_identifier is off, SQL server uses the quoted identifier setting of sp_dboption. For more information about database settings, see sp_dboption and set database options.

Set quoted_identifier is set during analysis. Setting during analysis means that the Set statement takes effect as long as it appears in a batch or stored procedure, and is irrelevant to whether the code execution actually reaches this point. The set statement takes effect before any statement is executed.

Permission
Set quoted_identifier permission is granted to all users by default.

Example
A. Use the referenced identifier to set and retain the word Object Name
The following example shows that set quoted_identifier must be set to on, and the keywords in the table name must be in double quotation marks to create and use object names with reserved keywords.

Set quoted_identifier off
Go
-- Attempt to create a table with a reserved keyword as a name
-- Shocould fail.
Create Table "select" ("Identity" int identity, "order" int)
Go

Set quoted_identifier on
Go

-- Will succeed.
Create Table "select" ("Identity" int identity, "order" int)
Go

Select "Identity", "order"
From "select"
Order by "order"
Go

Drop table "select"
Go

Set quoted_identifier off
Go

B. Use single quotation marks and double quotation marks in the referenced identifier settings
The following example shows how to use single quotation marks and double quotation marks in string expressions when set quoted_identifier is set to on and off.

Set quoted_identifier off
Go
Use pubs
If exists (select table_name from information_schema.views
Where table_name = 'test ')
Drop Table Test
Go
Use pubs
Create Table Test (ID int, string varchar (30 ))
Go

-- Literal strings can be in single or double quotation marks.
Insert into test values (1, "'text in single quotes '"
Insert into test values (2, '''text in single quotes ''')
Insert into test values (3, 'text with 2' ''' single quotes ')
Insert into test values (4, '"text in double quotes "')
Insert into test values (5, "" text in double quotes """
Insert into 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 "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 *
From Test
Go

Drop Table Test
Go

Set quoted_identifier off
Go

The following 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