SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER PROCEDURE RMS_SHOP_CHANGESPECIALITEM @ShopNumber int, @ItemKind int, @ItemIndex int, @ItemDurability int, @ItemCount int, @Price int, @InstantItem int AS set nocount on begin transaction

<think>我们正在讨论SQL Server存储过程中的SET选项配置，特别是`SET QUOTED_IDENTIFIER OFF`和`SET ANSI_NULLS OFF`对存储过程行为的影响。 根据引用[1]：当将ANSI_WARNINGS设置为OFF时，SQL Server数据库引擎遵循非标准行为，其中在聚合函数（如SUM, AVG, MAX, MIN或COUNT）中出现空值时不会发出警告。而引用[2]提到：为了执行分布式查询或在计算列或索引视图上创建或更改索引，应将SET CONCAT_NULL_YIELDS_NULL选项设置为ON。否则，操作将失败，并且SQL Server将返回错误，列出所有违反所需值的SET选项。SET CONCAT_NULL_YIELDS_NULL设置在运行时设置，而不是在解析时。 用户的问题是关于存储过程中`SET QUOTED_IDENTIFIER OFF`和`SET ANSI_NULLS OFF`的行为。在SQL Server中，存储过程的创建和运行会受到SET选项的影响，特别是当存储过程被创建时，某些SET选项会被捕获并成为存储过程执行计划的一部分。 具体来说： - `SET QUOTED_IDENTIFIER`：当设置为ON时，双引号内的内容被视为标识符（如表名、列名），而单引号用于字符串。当设置为OFF时，双引号被视为字符串的引号，与单引号作用相同。在存储过程中，这个设置是在创建存储过程时确定的，并且会影响到存储过程中SQL语句的解析。 - `SET ANSI_NULLS`：这个设置影响与空值比较的行为。当设置为ON时，在比较中如果有一个操作数为NULL，则结果也是NULL（遵循ANSI标准）。当设置为OFF时，两个空值比较会返回TRUE（例如`NULL=NULL`返回TRUE），而空值与任何非空值的比较返回FALSE。这个设置也是在存储过程创建时确定的。 在存储过程中，这些设置是存储过程创建时当前会话的SET设置的一部分，并且会永久保存。这意味着，即使存储过程被调用时会话的SET设置不同，存储过程执行时仍然使用创建时的设置。 但是，用户提到了`SET QUOTED_IDENTIFIER OFF`和`SET ANSI_NULLS OFF`，这表示他们想了解这些设置被关闭（OFF）时存储过程的行为。 1. **SET QUOTED_IDENTIFIER OFF**: - 在存储过程中，如果创建时`QUOTED_IDENTIFIER`为OFF，那么存储过程中的SQL语句可以使用双引号来包围字符串。但是，这可能会导致与使用双引号作为标识符的代码不兼容（例如，在对象名包含空格时，通常使用方括号`[]`而不是双引号）。 - 另外，如果存储过程中使用了索引视图或计算列上的索引，那么要求`QUOTED_IDENTIFIER`必须为ON（引用[2]提到分布式查询、计算列或索引视图需要特定的SET选项）。因此，如果设置为OFF，可能会影响这些功能的使用。 2. **SET ANSI_NULLS OFF**: - 当设置为OFF时，存储过程中关于空值的比较会采用非标准行为。例如，在WHERE条件中，`column = NULL`将会返回TRUE（如果该列值为NULL），而标准行为（ON）下，这种比较总是返回UNKNOWN（因此不会返回任何行）。这可能会导致存储过程的逻辑与预期不符，特别是当开发人员期望遵循ANSI标准行为时。 - 同样，在创建索引视图或计算列索引时，要求`ANSI_NULLS`必须为ON（引用[2]间接提到，因为错误会列出违反的SET选项）。 此外，引用[4]提到存储过程的优点之一就是执行速度快，因为它们是预编译和优化的。但是，如果SET选项设置不当，可能会影响存储过程的性能或功能。 因此，在调试存储过程时，如果遇到与空值处理或引号使用相关的问题，需要检查存储过程创建时的SET选项设置。 **如何查看存储过程的SET选项设置？** 在SQL Server中，可以通过`OBJECTPROPERTY`函数来查看存储过程的`QUOTED_IDENTIFIER`和`ANSI_NULLS`设置。例如： ```sql SELECT OBJECTPROPERTY(object_id('存储过程名'), 'ExecIsQuotedIdentOn') AS QuotedIdentifierStatus, OBJECTPROPERTY(object_id('存储过程名'), 'ExecIsAnsiNullsOn') AS AnsiNullsStatus; ``` 返回1表示ON，0表示OFF。 **建议**： 在创建存储过程时，通常建议保持这些SET选项为ON（即标准设置），以确保与大多数SQL代码的兼容性，并满足索引视图、计算列索引等的要求。如果确实需要非标准行为，应谨慎使用，并确保在存储过程内部有清晰的注释说明。 **相关问题**： 1. 在SQL Server中，存储过程创建时的SET选项设置如何影响存储过程的执行行为？ 2. 如何修改现有存储过程的SET选项设置？ 3. 除了`QUOTED_IDENTIFIER`和`ANSI_NULLS`，还有哪些SET选项在存储过程创建时被捕获？ 4. 为什么在创建索引视图时要求`ANSI_NULLS`和`QUOTED_IDENTIFIER`必须为ON？</think>### SQL Server存储过程中SET选项配置详解 在SQL Server存储过程中，`SET QUOTED_IDENTIFIER`和`SET ANSI_NULLS`是两个关键选项，它们会影响存储过程的编译和执行行为。这些设置通常在存储过程创建时被捕获并永久保存[^3]。 #### 1. **SET QUOTED_IDENTIFIER OFF** - **作用**：禁用标识符的双引号分隔规则 - **具体影响**： - 双引号`"`被视为普通字符而非标识符分隔符 - 必须使用方括号`[]`来分隔包含特殊字符的标识符（如`[Order Details]`） - 示例：`SELECT "name" FROM table` 将被解释为字符串`'name'`而非列名 - **注意事项**： - 影响索引视图和计算列索引的创建（可能导致失败）[^2] - 与ANSI标准不兼容，可能导致跨数据库兼容性问题 #### 2. **SET ANSI_NULLS OFF** - **作用**：改变NULL值的比较行为 - **具体影响**： - `WHERE column = NULL` 会返回TRUE（当列为NULL时） - 聚合函数（如SUM/AVG）忽略NULL值时不会发出警告[^1] - 示例： ```sql SET ANSI_NULLS OFF; SELECT * FROM Users WHERE Age = NULL; -- 会返回Age为NULL的行 ``` - **注意事项**： - 违反ANSI SQL标准，可能导致逻辑错误 - 影响分布式查询和计算列索引的创建[^2] #### 3. **配置建议与最佳实践** 1. **创建存储过程时的设置**： ```sql CREATE PROCEDURE dbo.ExampleProc AS SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF BEGIN -- 过程逻辑 END ``` - 这些设置会在**创建时固化**，后续执行不受会话设置影响 2. **调试建议**： - 检查存储过程定义： ```sql SELECT uses_ansi_nulls, uses_quoted_identifier FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.YourProcedure') ``` - `1`表示ON，`0`表示OFF - 使用标准设置（ON）避免意外行为： ```sql ALTER PROCEDURE dbo.YourProcedure WITH RECOMPILE AS SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON ``` 3. **性能影响**： - 非标准设置可能导致： - 索引视图/计算列索引不可用[^2] - 查询优化器生成次优执行计划 - 增加网络通信量（因逻辑错误需额外数据传输）[^4] #### 4. **典型问题场景** - **问题**：存储过程在测试环境正常，生产环境返回异常结果 - **原因**：开发环境`SET ANSI_NULLS OFF`而生产环境为ON - **解决方案**：统一使用`SET ANSI_NULLS ON` - **问题**：创建包含计算列索引的存储过程失败 - **原因**：`SET QUOTED_IDENTIFIER OFF` - **解决方案**：启用`SET QUOTED_IDENTIFIER ON`[^2] > **关键提示**：存储过程的SET选项在创建时确定，修改需重建过程。建议始终使用标准设置（ON）确保兼容性和可预测行为[^3][^4]。