了解 QUOTED_IDENTIFIER

Question

我们刚刚遇到了一个问题，我们的存储过程之一抛出了错误；

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

我通过修改存储过程并将带引号的标识符设置为 ON 来修复它。问题是，我在 CREATE PROCEDURE 调用之前执行了此操作。例如;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertStuff]

我本以为这会影响 CREATE PROCEDURE 语句，但不会影响与该过程的执行有关的任何内容。

我们的脚本全部部署为 drop 和 create 脚本并通过 sqlcmd 运行。我刚刚读过此处（搜索示例：执行SQLCMD) 和 此处 sqlcmd 在引用标识符关闭的情况下执行。我已经更改了我们的脚本以包含 -I 开关以查看是否可以解决我们的问题。

我的问题是：

1) SET QUOTED_IDENTIFIER ON 语句是否仅影响 DDL CREATE PROCEDURE 语句，还是也影响存储过程的执行？我的快速测试表明是后者。

2) 由于此开关的默认设置为 ON，我假设通过我设置 sqlcmd 查询的 -I 开关不会产生不利影响。出于所有意图和目的，我假设它与复制脚本内容然后将它们粘贴到查询管理器并点击执行相同。如果我对此有误，请纠正我。我们的简单部署脚本如下；

@echo off

SET dbodir=../Schema Objects/Schemas/dbo/Programmability/Stored Procedures/
SET tpmdir=../Schema Objects/Schemas/TPM/Programmability/Stored Procedures/

echo --- Starting dbo schema

for %%f in ("%dbodir%*.sql") do (echo Running %%f.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%dbodir%%%f")

echo --- Completed dbo schema

echo --- Starting TPM schema

for %%g in ("%tpmdir%*.sql") do (echo Running %%g.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%tpmdir%%%g")

echo --- Completed TPM schema

pause

提前致谢

编辑：

似乎还有一些进一步info 来确定存储过程的 SET 选项的存储位置，并且对此接受的答案提供了有关适用于 SET 选项的通用优先级顺序的一般规则的一些详细信息。对此的评论还指出；

“...在过程创建时仅捕获 QUOTED_IDENTIFER 和 ANSI_NULLS 设置。” “...SET QUOTED IDENTIFIER 无法在运行时设置在存储过程中”（我的重点）。

我觉得这回答了我的第一个问题。

还有人想看第二部吗？

Answer 1

为了寻求对QUOTED_IDENTIFIER的理解，我将在这里发布一些理解。

简短版本

ANSI 要求在标识符周围使用引号（而不是在字符串周围）。 SQL Server 支持以下两者：

SQL Server 最初：

• SELECT "Hello, world!" --引号
• SELECT 'Hello, world! ' --撇号
• CREATE TABLE [世界上最糟糕的表名] ([Hello, world!] int)
• SELECT [Hello, world!] 】 FROM 【世界上最可怕的桌子name]

ANSI（即SET QUOTED_IDENTIFIER ON）：

• SELECT "Hello, world!" -- ANSI 中字符串周围的引号不再有效
• SELECT 'Hello, world!' --撇号
• CREATE TABLE "The world's most糟糕的表名” （“你好，世界！”int）
• SELECT“你好，世界！” FROM “世界上最糟糕的表名”

长版本

最初，SQL Server 允许您使用引号 ("...") 和 字符串周围的撇号（'...'）可互换（就像 Javascript 一样）：

• SELECT "Hello, world!" --引号< /em>
• 选择“你好，世界！” --撇号

如果您想要一个名称表、视图、过程、列等，并且其名称会违反命名对象的所有规则，您可以将其括在方括号 ([, ])：

CREATE TABLE [The world's most awful table name] ([Hello, world!] int)
SELECT [Hello, world!] FROM [The world's most awful table name]

这一切都有效，而且有意义。

然后出现了 ANSI

然后 ANSI 出现了，并提出了其他想法：

• 如果您有一个时髦的名称，请将其用引号（“...”）括起来，
• 使用撇号 ('...') 对于字符串
• ，我们甚至不关心你的方括号

这意味着如果你想“引用”一个时髦的列名或表名必须使用引号：

SELECT "Hello, world!" FROM "The world's most awful table name"

如果您了解 SQL Server，则知道引号已经被用来表示字符串。如果您盲目地尝试像执行 T-SQL 一样执行 ANSI-SQL：这是无稽之谈，SQL Server 是这样告诉您的：

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'The world's most awful table name'.

这在道德上等同于尝试执行：

SELECT 'Hello, world!' FROM 'The world''s most awful table name'

这就像执行：

SELECT 'string' FROM 'string'

您必须选择加入新的 ANSI 行为

因此，Microsoft 添加了一项功能来让您选择加入 ANSI 风格的 SQL。

原始 （或QUOTED_IDENTIFIER关闭）：

SELECT "Hello, world!" --valid
SELECT 'Hello, world!' --valid

SET QUOTED_IDENTIFIER ON：

SELECT "Hello, world!" --INVALID
SELECT 'Hello, world!' --valid

SQL Server仍然允许您使用[square方括号]，而不是强迫您使用“引号”。但当 QUOTED_IDENTIFIER ON 时，您不能使用“字符串周围的双引号”，您只能使用“单引号撇号”。

Answer 2

我将以下命令保存到文本文件，然后使用 SQLCMD 执行它：

SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF

在 SQL 探查器中检查，SQLCMD -i 连接到我的系统上的以下连接选项：

-- network protocol: LPC
set quoted_identifier on
...

但是，以下命令由SQLCMD 连接时：

SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096

然后运行我的脚本。

因此，2) 的答案是否定的 - 使用 SQLCMD -i 运行脚本与从 SSMS 执行（使用默认连接选项）不同。如果脚本需要QUOTED_IDENTIFIER ON，那么如果您要以这种方式执行它，则需要在开始时显式设置它。

Answer 3

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO
--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

ANSI NULL ON/OFF:

此选项指定 ANSI NULL 比较的设置。当此选项打开时，任何将值与 null 进行比较的查询都会返回 0。关闭时，任何将值与 null 进行比较的查询都会返回 null 值。

QUOTED IDENTIFIER ON/OFF:

该选项指定双引号的使用设置。启用此选项后，双引号将用作 SQL Server 标识符（对象名称）的一部分。这在标识符也是 SQL Server 保留字的情况下非常有用。

Answer 4

关于您的问题#1，原因在使用时的注意事项中说明SET 语句。它指出：

存储过程使用执行时指定的 SET 设置执行，SET ANSI_NULLS 和 SET QUOTED_IDENTIFIER 除外。指定 SET ANSI_NULLS 或 SET QUOTED_IDENTIFIER 的存储过程使用在存储过程创建时指定的设置。如果在存储过程中使用，则任何 SET 设置都会被忽略。