sql-server - 了解 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 运行。我刚刚读过here (search for Example: Executing SQLCMD)和 here 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

提前致谢

编辑:

似乎有一些 further 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 [世界上最糟糕的表名]

ANSI(即SET QUOTED_IDENTIFIER ON):

• SELECT "Hello, world!" --ANSI 字符串周围的引号不再有效
• SELECT 'Hello, world!' --撇号
• CREATE TABLE“世界上最糟糕的表名”(“Hello，world!”int)
• SELECT“你好，世界!” FROM“世界上最难听的 table 名”

长版本

最初，SQL Server 允许您使用引号 ("...") 和撇号 ('.. .') 可以互换地围绕字符串(就像 Javascript 一样):

• SELECT "Hello, world!" --引号
• SELECT 'Hello, world!' --撇号

如果您想要一个名称表、 View 、过程、列等，其中包含一些违反命名对象的所有规则的内容，您可以将其括在方括号中([，]):

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

设置 QUOTED_IDENTIFIER ON:

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

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