Início / dba / Perguntas / 126632
Accepted
Artashes Khachatryan
Asked: 2016-01-20 05:48:20 +0800 CST

Aviso sobre conversão implícita

  • 772

Eu tenho uma tabela com 2 colunas de nome:

CREATE TABLE Test
(
  TestID int identity primary key clustered
  , Name_Eng nvarchar(50)
  , Name_Nat nvarchar(50)
)

Agora preciso de uma query para pegar essa Namecoluna separada por ,, assim:

    DECLARE @NameColumns NVARCHAR(1024)

            SET @NameColumns = STUFF(
      (SELECT   ',' + 'Test.' + name AS [text()]
       FROM     ( SELECT    c.name
                  FROM      sys.columns c 
                            INNER JOIN sys.tables t ON t.object_id = c.object_id
                  WHERE     t.name = 'Test'
                            AND c.name LIKE 'Name_%'
                ) AS D
                FOR                  XML PATH('') ,
                                         TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1,
                                     N'')

select  @NameColumns

Mas esta consulta tem um aviso no plano de execução:

aviso de conversão implícita

Existe alguma maneira de remover esse aviso?

sql-server performance

3 respostas

  1. Best Answer

    O aviso existe por causa da função XML value(). O segundo parâmetro value()é o que você deseja que o valor armazenado no XML seja convertido. Você poderia argumentar que esta não é de fato uma conversão implícita, mas uma conversão muito explícita, já que você está pedindo que isso aconteça. Talvez algo para um item de conexão sugerir à Microsoft.

    Maneira mais simples de reproduzir o que você vê.

    declare @X xml;
select @X.value('text()[1]', 'int');

    Dá esses dois avisos.

    <Warnings>
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0)" />
</Warnings>

    Como você pode ver, você também obtém int's e obtém dois deles para cada invocação de value().

    declare @X xml;
select @X.value('text()[1]', 'int'),
       @X.value('text()[1]', 'bit');

    <Warnings>
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(bit,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(bit,XML Reader with XPath filter.[value],0)" />
</Warnings>

    A conversão é feita no operador Stream Aggregate que calcula o valor desta forma.

    MIN(CASE WHEN [@X] IS NULL 
      THEN NULL 
      ELSE 
      CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) 
        THEN CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0) 
        ELSE CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0) 
      END 
    END)

    O resultado da função Table Valued é retornado na coluna lvalueou . valueA expressão verifica usando datalengthpara descobrir de onde deve ser buscada e, em seguida, converte-a no tipo de dados desejado.

    Existe alguma maneira de remover esse aviso?

    Sim existe. Você remove a TYPEdiretiva da FOR XML PATHinstrução e remove a chamada para a value()função. Um efeito colateral de fazer isso é que os valores que você concatena que contêm caracteres que precisam ser codificados no XML como &<>serão codificados em seu resultado.

    • 10

  2. Embora eu concorde com @Kin sobre os tipos de dados, não acho que esse aviso seja tão problemático quanto você pensa. Você está executando a concatenação agrupada, que será muito mais cara do que qualquer conversão de qualquer maneira (e, como disse Daniel, a menos que suas visualizações de catálogo sejam massivas - como em maior que a memória física - é improvável que afete qualquer coisa em termos de estimativa também).

    Eu escreveria a consulta dessa maneira, tomando cuidado para nunca usar strings varchar (o que também significa parar de deixar de fora o Nprefixo em strings literais) e também garantir que você use terminadores de instrução :

    DECLARE @NameColumns nvarchar(max); -- why 1024 when you use max below?

SET @NameColumns = STUFF(
  (SELECT   N',Test.' + name AS [text()] FROM    
    (
      SELECT c.name FROM sys.columns AS c 
        INNER JOIN sys.tables AS t 
          ON t.object_id = c.object_id
        WHERE t.name = N'Test'
        AND   c.name LIKE N'Name_%'
    ) AS D FOR XML PATH(N''),
    TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

SELECT @NameColumns;

    Mesmo assim, não acredito que haja alguma maneira de evitar a conversão implícita sem alterar a saída evitando TYPE/ value()como Mikael sugeriu . Eu diria que se preocupe com isso quando você puder provar que isso tem algum impacto material no desempenho da consulta. Em meus testes, as duas formas diferentes tiveram o mesmo desempenho (estamos falando de sub-10ms todas as vezes), mas é claro que se eu tiver uma tabela nomeada Sales & Stuff, ela ficará Sales &amp; Stuffsem a conversão implícita.

    • 9

  3. O aviso no plano de consulta significa que, como você tem uma conversão implícita de tipo de dados, o SQL Server não poderá adivinhar com precisão o número correto de linhas retornadas, o que, por sua vez, pode levar a um plano abaixo do ideal.

    Isso é importante em consultas que precisam ter um bom desempenho, normalmente porque trabalham com muitos dados, mas na sua situação, não parece ser o caso, pois você está apenas consultando sys.tablese sys.columns.

    A resposta curta para sua pergunta é: não importa, a menos que você tenha milhões e milhões de tabelas e colunas em seu banco de dados.

    • 3

relate perguntas