sql-server - IF 语句中的 SQL Server 排序规则问题(消息 468，级别 16，状态 9)

Question

我的问题出现在“简单易行”的 IF 语句中，使得对许多类似问题(例如 Cannot resolve the collation conflict in my query)的建议修复看起来毫无用处。

错误信息是:

Msg 468, Level 16, State 9, Procedure #XYZ, Line 11
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

已知服务器排序规则设置为 SQL_Latin1_General_CP1_CI_AS。

这个查询演示了问题:

-- this procedure (which gets put into tempdb) is called WITHOUT specifying @Choice  
CREATE PROCEDURE #XYZ 
(
    -- all other parameters removed (none of them have default values)
    @Choice AS NVARCHAR(1) = 'Y' 
)
AS  
BEGIN
    IF (@choice = 'Y')  -- error raised here 
    BEGIN
        DECLARE @NULL_STATEMENT AS int -- only here because there's no "do nothing" statement
    END 
    RETURN
END

如果更改服务器(和/或所有表)的默认排序规则不会发生，并且在所有查询、表等中插入“COLLATE DATABASE_DEFAULT”是不切实际的，我该如何解决这个问题。 (对于此解决方案，请参阅 https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/ 和 Cannot resolve the collation conflict between temp table and sys.objects)。

密切相关的链接:

COLLATE 子句的文档:https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

我可能无法使用的解决方案:https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/

Answer 1

(来自当前接受的答案):

The fix was to change the default parameter from ASCII (varchar) to nvarchar (UTF-8) form

1. 不，这并没有解决问题。该特定更改没有影响，因为在解析 T-SQL 时值被转换为 NVARCHAR(由于参数/变量为 NVARCHAR)并且此错误发生在编译时时间。
2. 此问题与NVARCHAR、UTF-8 甚至参数默认值完全无关。
3. 此处未使用 UTF-8。 SQL Server 只看到 UTF-16，因为这是驱动程序传输的 Unicode 字符串(即 TDS/表格数据流)。即使在使用 UTF-8 排序规则(SQL Server 2019 中的新功能)时，该编码也仅用于 VARCHAR 类型，因为 NVARCHAR 永远只是 UTF-16(Little Endian ).

您遇到的问题是在临时存储过程(本地和全局)中发现的几个“奇怪”行为之一。对于临时存储过程，参数和变量将始终具有与 tempdb 排序规则匹配的排序规则，而字符串文字将使用执行 CREATE PROCEDURE 语句的数据库的排序规则。这两个排序规则不会改变(对于模块的主要 T-SQL 上下文)，即使您使用另一个具有不同于 [tempdb] 的默认排序规则的数据库和临时 proc 所在的数据库创建(尽管在临时存储过程中执行的动态 SQL 将使用当前 DB 的排序规则!很有趣，嗯？)。

因此，正如 Martin Smith 在对该问题的评论中所说，在参数值前缀为 后执行 CREATE PROCEDURE 语句时，您必须更改“当前”/“事件”数据库>N.

以下示例是问题中显示的代码的简化版本，但清楚地表明在字符串文字前加上 N 并不能防止错误。

在与 [tempdb] 具有不同排序规则的数据库中执行以下 T-SQL:

-- The two returned collations need to be different, else no error with CREATE PROC:
SELECT DATABASEPROPERTYEX(N'tempdb', 'collation') AS [tempdb collation],
       DATABASEPROPERTYEX(DB_NAME(), 'collation') AS [current DB collation];


SET NOEXEC ON;

GO
CREATE PROCEDURE #XYZ
(
  @Choice NVARCHAR(5) = N'Y'
)
AS  
BEGIN
  IF (@Choice = N'Y') PRINT 'yup';
END;
GO

SET NOEXEC OFF;

/*
Msg 468, Level 16, State 9, Procedure #XYZ, Line XXXXX [Batch Start Line YYYYY]
   Cannot resolve the collation conflict between "{current_DB_collation}" and
   "{tempdb_collation}" in the equal to operation.
*/

(来自当前接受的答案):

The weird thing is that after running that version I was able to remove the leading N and the query worked without problems.

正确。这是因为 N 前缀实际上与错误或修复错误没有任何关系。您只是在一个具有 SQL_Latin1_General_CP1_CI_AS 排序规则的数据库中，该排序规则与 [tempdb] 排序规则相匹配。

---

_{我正在写一篇博文，其中详细介绍了临时存储过程的几种奇怪行为，包括这种整理。如果/当我完成它时，我会尽量记得用它的链接更新这个答案。}