主页 / dba / 问题 / 317945
Accepted
Lukas.Navratil
Asked: 2022-10-08 01:20:08 +0800 CST

比较 sql_variant 与 varchar 文字时的排序规则冲突

  • 772

我发现下面的查询在运行相同兼容性级别、相同设置选项等的两个 Azure 超大规模数据库上返回不同的结果。

IF CAST(ServerProperty('Edition') AS nvarchar(128)) = 'SQL Azure' BEGIN
    SELECT 1
END
IF ServerProperty('Edition') = 'SQL Azure' BEGIN
    SELECT 2
END
IF 'SQL Azure' = ServerProperty('Edition') BEGIN
    SELECT 3
END

在一个数据库上，它只返回 1，在另一个数据库上它返回 1,2 和 3。

我调查了根本原因，它似乎是由数据库的不同排序规则引起的。

对于以下查询：

SELECT SQL_VARIANT_PROPERTY(ServerProperty('Edition'), 'Collation')
SELECT name, collation_name, compatibility_level FROM sys.databases

只返回一行的数据库，结果是：

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP850_CI_AS   150

数据库返回 1,2,3 的结果是：

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP1_CI_AS     150

因此，没有强制转换的简单比较是sql_variant与varchar（当我使用时没有区别N'SQL Azure'）比较，其中底层nvarchar的sql_variant排序规则在一种情况下与我查询的数据库不同，在另一种情况下它是匹配的。

首先，我假设比较具有不同排序规则的两个字符串会失败，就像当您尝试加入具有不同排序规则的两列时它会失败一样，但这里显然不是这种情况。

无论如何，安全比较可能sql_variant与 a的函数的输出的最佳方法是varchar什么？

sql-server collation

2 个回答

  1. Best Answer

    该sql_variant类型位于数据优先级层次结构的顶部，因此字符串文字sql_variant在进行比较之前被隐式转换为。

    比较规则包括sql_variant（强调）：

    • 当比较不同基本数据类型的sql_variant值，并且基本数据类型属于不同的数据类型族时，层次图中数据类型族较高的值被认为是两个值中的较大者。
    • 当比较不同基本数据类型的sql_variant值且基本数据类型属于同一数据类型族时，层次图中基本数据类型较低的值会隐式转换为其他数据类型，然后进行比较。
    • 比较 char、varchar、nchar 或 nvarchar 数据类型的 sql_variant 值时，首先根据以下标准比较它们的排序规则：LCID、LCID 版本、比较标志和排序 ID。这些标准中的每一个都作为整数值进行比较，并按照列出的顺序进行比较。如果所有这些条件都相等，则根据排序规则比较实际的字符串值。

    这解释了你看到的结果。

    此外：_

    在处理 sql_variant 数据类型时，SQL Server 支持将具有其他数据类型的对象隐式转换为 sql_variant 类型。但是，SQL Server 不支持从 sql_variant 数据到具有另一种数据类型的对象的隐式转换。

    排序优先级的规则是分开的。它们适用于比较具有不同排序规则标签的字符串。有关Coercible-default、Implicit X、Explicit X和No- collat​​ion 标签的详细信息，请参阅链接文档。

    sql_variant使用当前数据库的排序规则将返回值转换为基类型。这将匹配分配给文字值的排序规则，它应该具有 N 前缀以被解释为 Unicode。

    就我而言，这意味着：

    IF CONVERT(nvarchar(128), SERVERPROPERTY('Edition')) COLLATE DATABASE_DEFAULT = 
    N'Developer Edition (64-bit)'

    如果您完全按照预期提供字符串文字，则该COLLATE子句不是绝对必要的，但您确实要求“最佳”方式，我认为这是“最全面”的意思。

    我的捷克伙伴 Tomáš Zíka 在Expecting Subvertations中写了关于这个和相关问题的文章。

    • 7

  2. 将可能是 sql_variant 的函数的输出与 varchar 进行安全比较的最佳方法是什么？

    对于SERVERPROPERTYreturn nvarchar，我建议您已经发现的解决方法加上一个nvarchar常量而不是varchar.

    显式转换 1) 将返回nvarchar当前数据库排序规则中的一个；2) 避免将 varchar 常量隐式转换为 sql_variant。该nvarchar常量也将是当前数据库排序规则并匹配显式转换类型，从而允许更可靠的相等比较。

    IF CAST(ServerProperty('Edition') AS nvarchar(128)) = N'SQL Azure'
    • 4

相关问题