SET QUOTED_IDENTIFIER (Transact-SQL)SET QUOTED_IDENTIFIER (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

使 SQL ServerSQL Server 遵从关于引号分隔标识符和文字字符串的 ISO 规则。Causes SQL ServerSQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. 由双引号分隔的标识符可以是 Transact-SQLTransact-SQL 保留关键字,也可以包含 Transact-SQLTransact-SQL 标识符语法约定通常不允许的字符。Identifiers delimited by double quotation marks can be either Transact-SQLTransact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQLTransact-SQL syntax rules for identifiers.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database

SET QUOTED_IDENTIFIER { ON | OFF }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

SET QUOTED_IDENTIFIER ON

RemarksRemarks

当 SET QUOTED_IDENTIFIER 为 ON 时,标识符可以由双引号分隔,而文字必须由单引号分隔。When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. 当 SET QUOTED_IDENTIFIER 为 OFF 时,标识符不可加引号,且必须符合所有 Transact-SQLTransact-SQL 标识符规则。When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQLTransact-SQL rules for identifiers. 有关详细信息,请参阅 Database IdentifiersFor more information, see Database Identifiers. 文字可以由单引号或双引号分隔。Literals can be delimited by either single or double quotation marks.

当 SET QUOTED_IDENTIFIER 为 ON(默认值)时,由双引号分隔的所有字符串都被解释为对象标识符。When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. 因此,加引号的标识符不必符合 Transact-SQLTransact-SQL 标识符规则。Therefore, quoted identifiers do not have to follow the Transact-SQLTransact-SQL rules for identifiers. 它们可以是保留关键字,并且可以包含 Transact-SQLTransact-SQL 标识符中通常不允许的字符。They can be reserved keywords and can include characters not generally allowed in Transact-SQLTransact-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 必须为 ON。SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

当 SET QUOTED_IDENTIFIER 为 OFF 时,表达式中的文字字符串可以由单引号或双引号分隔。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 必须为 ON。SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. 如果 SET QUOTED_IDENTIFIER 为 OFF,则计算列或索引视图上带索引的表上的 CREATE、UPDATE、INSERT 和 DELETE 语句将失败。If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. 有关计算列的索引视图和索引需要的 SET 选项设置的详细信息,请参阅 SET 语句中的“使用 SET 语句时的注意事项”。For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements.

在创建筛选索引,SET QUOTED_IDENTIFIER 必须为 ON。SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.

在调用 XML 数据类型方法时,SET QUOTED_IDENTIFIER 必须为 ON。SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

在进行连接时,SQL ServerSQL Server Native Client ODBC 驱动程序和 SQL ServerSQL Server Native Client OLE DB Provider for SQL ServerSQL Server 自动将 QUOTED_IDENTIFIER 设置为 ON。The SQL ServerSQL Server Native Client ODBC driver and SQL ServerSQL Server Native Client OLE DB Provider for SQL ServerSQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. 这可以在 ODBC 数据源、ODBC 连接特性或 OLE DB 连接属性中进行配置。This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. 对来自 DB-Library 应用程序的连接,SET QUOTED_IDENTIFIER 默认设置为 OFF。The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

创建表时,即使此时将 QUOTED IDENTIFIER 选项设置为 OFF,该选项在表的元数据中仍始终存储为 ON。When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

创建存储过程时,将捕获 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置,并用于该存储过程的后续调用。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.

在存储过程内执行 SET QUOTED_IDENTIFIER 时,其设置不更改。When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

当 SET ANSI_DEFAULTS 为 ON 时,将启用 SET QUOTED_IDENTIFIER。When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER 还与 ALTER DATABASE 的 QUOTED_IDENTIFIER 设置相对应。SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE. 有关数据库设置的详细信息,请参阅 ALTER DATABASEFor more information about database settings, see ALTER DATABASE.

SET QUOTED_IDENTIFIER 在分析时生效,只会影响分析,不影响查询执行。SET QUOTED_IDENTIFIER takes effect at parse-time and only affects parsing, not query execution.

对于顶级即席批处理,请使用会话的当前 QUOTED_IDENTIFIER 设置开始分析。For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. 分析批处理时,只要出现 SET QUOTED_IDENTIFIER,就会更改之后的分析行为,并保存会话的设置。As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. 因此,分析和执行批处理后,会根据批处理中最后一次出现的 SET QUOTED_IDENTIFIER,设置会话的 QUOTED_IDENTIFER 设置。So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

存储过程中的静态 SQL 是使用对于创建或更改存储过程的批处理有效的 QUOTED_IDENTIFIER 设置分析的。Static 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 作为静态 SQL 出现在存储过程的正文中时是无效的。SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static SQL.

对于使用 sp_executesql 或 exec() 的嵌套批处理,使用会话的 QUOTED_IDENTIFIER 设置开始进行分析。For a nested batch using sp_executesql or exec() the parsing begins using the QUOTED_IDENTIFIER setting of the session. 如果嵌套批处理在存储过程内,则使用存储过程的 QUOTED_IDENTIFIER 设置开始进行分析。If the nested batch is inside a stored procedure the parsing starts using the QUOTED_IDENTIFIER setting of the stored procedure. 分析嵌套批处理时,只要出现 SET QUOTED_IDENTIFIER,就会改变之后的分析行为,但不会更新会话的 QUOTED_IDENTIFIER 设置。As the nested batch is parsed, any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, but the session's QUOTED_IDENTIFIER setting will not be updated.

QUOTED_IDENTIFIER 设置不影响使用括号 [ 和 ] 分隔标识符。 Using brackets, [ and ], to delimit identifiers is not affected by the QUOTED_IDENTIFIER setting.

要查看此设置的当前设置,请运行以下查询。To view the current setting for this setting, run the following query.

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

权限Permissions

要求具有 public 角色的成员身份。Requires membership in the public role.

示例Examples

A.A. 使用加引号的标识符设置和保留字对象名Using the quoted identifier setting and reserved word object names

以下示例显示 SET QUOTED_IDENTIFIER 设置必须为 ON,而且表名内的关键字必须在双引号内,才能创建和使用具有保留关键字名称的对象。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 that have reserved keyword names.

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
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.B. 使用加单引号和双引号的标识符设置Using the quoted identifier setting with single and double quotation marks

以下示例显示将 SET QUOTED_IDENTIFIER 设置为 ONOFF 时,在字符串表达式中使用单引号和双引号的方式。The following 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 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

另请参阅See Also