Export (0) Print
Expand All
19 out of 24 rated this helpful - Rate this topic

SET QUOTED_IDENTIFIER

SQL Server 2000

Causes Microsoft® SQL Server™ to follow the SQL-92 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 usually allowed by the Transact-SQL syntax rules for identifiers.

Syntax

SET QUOTED_IDENTIFIER { ON | OFF }

Remarks

When SET QUOTED_IDENTIFIER is ON (default), 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 Using Identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON, 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 usually 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 creating or manipulating 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 Using SET Statements in SET.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. SET QUOTED_IDENTIFIER defaults to OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's meta data even if the option is set to OFF when the table is created.

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

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

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 Setting Database Options.

SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means that if the SET statement is present in the batch or stored procedure, it takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed.

Permissions

SET QUOTED_IDENTIFIER permissions default to all users.

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

This 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
-- Attempt to create a table with a reserved keyword as a name
-- should 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 the quoted identifier setting with single and double quotes

This example shows the way 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 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

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     

See Also

CREATE DATABASE

CREATE DEFAULT

CREATE PROCEDURE

CREATE RULE

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

Data Types

EXECUTE

SELECT

SET

SET ANSI_DEFAULTS

sp_rename

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.