Início / dba / Perguntas / 63743
Accepted
Mark Sinkinson
Asked: 2014-04-24 06:31:56 +0800 CST

Convertendo um VARCHAR para VARBINARY

  • 772

Tenho mantido um registro de consultas caras em execução, juntamente com seus planos de consulta, em uma tabela para nos permitir monitorar tendências de desempenho e identificar áreas que precisam ser otimizadas.

No entanto, chegou ao ponto em que os planos de consulta estão ocupando muito espaço (já que estamos armazenando o plano inteiro em cada consulta).

Estou, portanto, tentando normalizar os dados existentes extraindo o QueryPlanHash e o QueryPlan para outra tabela.

CREATE TABLE QueryPlans
(
    QueryPlanHash VARBINARY(25),
    QueryPlan XML,
    CONSTRAINT PK_QueryPlans PRIMARY KEY
    (
      QueryPlanHash
    )
);

Como a definição de query_plan_hashin sys.dm_exec_query_statsé um campo binário (e estarei inserindo novos dados regularmente), eu estava usando VARBINARYpara o tipo de dados na minha nova tabela.

No entanto, a inserção abaixo falha ...

INSERT INTO QueryPlans
    ( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
    SELECT 
      p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
      QueryPlan,
      ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
    FROM table
    CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1

....com o erro

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

O problema é que os hashes do plano de consulta já estão em formato binário, porém armazenados como VARCHAR no Plano de Consulta XML, por exemplo

0x9473FBCCBC01AFE

e CONVERT to BINARY dá um valor completamente diferente

0x3078393437334642434342433031414645

Tentei alterar a definição de valor na seleção de XQuery para binário, mas não retornou nenhum valor.

Como eu extrairia o valor de 0x9473FBCCBC01AFEum plano de consulta XML como um VARBINARY, em vez de um VARCHAR?

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Você precisa usar um estilo específico quando espera manter o mesmo valor binário ao converter de uma string. Caso contrário, o SQL Server tentará codificar a string da mesma maneira que codificaria 'bob'ou 'frank'.

    Dito isto, sua string de entrada não parece correta - há um byte ausente ou um byte a mais. Isso funciona bem se eu soltar o final E:

    SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);
------------ the ,1 is important ---------------^^^

    O resultado é binário:

    ----------------
0x9473FBCCBC01AF
    • 33

  2. Como extrair o valor de 0x9473FBCCBC01AFE de um plano de consulta XML como VARBINARY, em vez de VARCHAR?

    Eu enfrentei algo assim usando HeidiSQL para consultar tabelas CASD e resolvi com fn_varbintohexstr() , assim:

    SELECT master.dbo.fn_varbintohexstr(table.hexfield) FROM table;

    Com HeidiSQL, o valor estava errado como '0x3F3F3F3F3F3F3F3F' e ficou correto como '0x158B1DB75616484695684007CE98E21C'.

    OBS: Funciona desde MSSQL 2008! Espero que ajude!

    • 0

relate perguntas