主页 / dba / 问题 / 63743
Accepted
Mark Sinkinson
Asked: 2014-04-24 06:31:56 +0800 CST

将 VARCHAR 转换为 VARBINARY

  • 772

我一直在一个表中记录昂贵的运行查询及其查询计划，以便我们监控性能趋势并确定需要优化的区域。

但是，查询计划占用了太多空间（因为我们针对每个查询存储整个计划）。

因此，我试图通过将 QueryPlanHash 和 QueryPlan 提取到另一个表来规范化现有数据。

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

由于query_plan_hashin的定义sys.dm_exec_query_stats是二进制字段（并且我会定期插入新数据），因此我将其VARBINARY用于新表中的数据类型。

但是，下面的插入失败...

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

....错误

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

问题是查询计划哈希已经是二进制格式，但是在 XML 查询计划中存储为 VARCHAR，例如

0x9473FBCCBC01AFE

并且 CONVERT to BINARY 给出完全不同的值

0x3078393437334642434342433031414645

我尝试将 XQuery 选择中的值定义更改为二进制，但随后它没有返回任何值。

如何0x9473FBCCBC01AFE从 XML 查询计划中提取 的值作为 aVARBINARY而不是 a VARCHAR？

sql-server sql-server-2012

2 个回答

  1. Best Answer

    当您希望在从字符串转换时保持相同的二进制值时，您需要使用特定的样式。否则，SQL Server 会尝试以与编码'bob'或相同的方式对字符串进行编码'frank'。

    也就是说，您的输入字符串看起来不正确 - 缺少一个字节或一个字节太多。如果我放弃尾随，这很好用E：

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

    结果是二进制的：

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

  2. 如何从 XML 查询计划中提取 0x9473FBCCBC01AFE 的值作为 VARBINARY，而不是 VARCHAR？

    我遇到了类似的问题，使用 HeidiSQL 查询 CASD 表，并使用fn_varbintohexstr()解决，如下所示：

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

    使用 HeidiSQL，该值像“0x3F3F3F3F3F3F3F3F3F”这样错误，而像“0x158B1DB75616484695684007CE98E21C”这样正确。

    OBS：从 MSSQL 2008 开始工作！希望能帮助到你！

    • 0

相关问题