Início / dba / Perguntas / 317945
Accepted
Lukas.Navratil
Asked: 2022-10-08 01:20:08 +0800 CST

Conflito de agrupamento ao comparar sql_variant com literal varchar

  • 772

Descobri que as consultas abaixo retornam resultados diferentes em dois bancos de dados de hiperescala do Azure que estão executando o mesmo nível de compatibilidade, as mesmas opções de conjunto etc.

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

Em um banco de dados, ele retorna apenas 1, em outro banco de dados retorna 1,2 e 3.

Eu investiguei a causa raiz e parece ser causada por diferentes agrupamentos dos bancos de dados.

Para as seguintes consultas:

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

O banco de dados que retorna apenas uma linha, o resultado é:

-----------------------------
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

E o resultado do banco de dados que retorna 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

Portanto, a comparação simples sem o elenco está comparando sql_variantcom varchar(não há diferença quando eu uso N'SQL Azure') onde o subjacente nvarchardo sql_varianttem em um caso um agrupamento diferente do banco de dados que eu consulto e, em outro caso, é correspondente.

Em primeiro lugar, eu diria que a comparação de duas strings com agrupamento diferente falharia como falha quando você tenta unir duas colunas com agrupamento diferente, mas aparentemente não é o caso aqui.

De qualquer forma, qual é a melhor maneira de comparar com segurança a saída de uma função que pode estar sql_variantcom um varchar?

sql-server collation

2 respostas

  1. Best Answer

    O sql_varianttipo está no topo da hierarquia de precedência de dados, portanto, o literal de cadeia de caracteres é convertido implicitamente sql_variantantes que a comparação ocorra.

    As regras para sql_variantcomparação incluem (grifo nosso):

    • Quando os valores sql_variant de diferentes tipos de dados de base são comparados e os tipos de dados de base estão em diferentes famílias de tipos de dados, o valor cuja família de tipos de dados é mais alta no gráfico de hierarquia é considerado o maior dos dois valores.
    • Quando os valores sql_variant de diferentes tipos de dados base são comparados e os tipos de dados base estão na mesma família de tipos de dados, o valor cujo tipo de dados base é inferior no gráfico de hierarquia é convertido implicitamente para o outro tipo de dados e a comparação é feita.
    • Quando os valores sql_variant dos tipos de dados char, varchar, nchar ou nvarchar são comparados, seus agrupamentos são comparados primeiro com base nos seguintes critérios: LCID, versão do LCID, sinalizadores de comparação e ID de classificação. Cada um desses critérios é comparado como valores inteiros e na ordem listada. Se todos esses critérios forem iguais , os valores de string reais serão comparados de acordo com o agrupamento.

    Isso explica os resultados que você viu.

    Além disso :

    Ao manipular o tipo de dados sql_variant, o SQL Server oferece suporte a conversões implícitas de objetos com outros tipos de dados para o tipo sql_variant. No entanto, o SQL Server não oferece suporte a conversões implícitas de dados sql_variant para um objeto com outro tipo de dados.

    As regras para precedência de agrupamento são separadas. Eles se aplicam ao comparar strings com diferentes rótulos de agrupamento. Consulte a documentação vinculada para obter detalhes detalhados dos rótulos Coercible-default , Implicit X , Explicit X e No-collation .

    Converta o sql_variantvalor de retorno para o tipo base com o agrupamento do banco de dados atual. Isso corresponderá ao agrupamento atribuído ao valor literal, que deve ter o prefixo N para ser interpretado como Unicode.

    Para o meu caso, isso significa:

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

    A COLLATEcláusula não é estritamente necessária se você fornecer a string literal exatamente como esperado, mas você pediu a 'melhor' maneira, que eu entendi como 'mais abrangente'.

    Meu colega tcheco Tomáš Zíka escreveu sobre isso e assuntos relacionados em Expecting Subvertations .

    • 7

  2. qual é a melhor maneira de comparar com segurança a saída de uma função que pode ser sql_variant com um varchar?

    Para SERVERPROPERTYesse retorno nvarchar, sugiro a solução alternativa que você já descobriu mais uma nvarcharconstante em vez de varchar.

    A conversão explícita 1) retornará um nvarchardo agrupamento de banco de dados atual e 2) evitará a conversão implícita da constante varchar para sql_variant. A nvarcharconstante também será o agrupamento de banco de dados atual e corresponderá ao tipo de conversão explícito, permitindo uma comparação de igualdade mais confiável.

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

relate perguntas