Konwertowanie VARCHAR na VARBINARY

Prowadzę dziennik drogich uruchomionych zapytań wraz z ich planami zapytań w tabeli, abyśmy mogli monitorować trendy w wydajności i identyfikować obszary wymagające optymalizacji.

Doszło jednak do tego, że plany zapytań zajmują zbyt dużo miejsca (ponieważ przechowujemy cały plan dla każdego zapytania).

Dlatego próbuję znormalizować istniejące dane, wyodrębniając QueryPlanHash i QueryPlan do innej tabeli.

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

Ponieważ definicja query_plan_hashin sys.dm_exec_query_statsjest polem binarnym (i będę regularnie wstawiał nowe dane), użyłem VARBINARYdla typu danych w mojej nowej tabeli.

Poniższa wstawka nie działa jednak ...

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

.... z błędem

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

Problem polega na tym, że skróty planu zapytań są już w formacie binarnym, jednak są przechowywane jako VARCHAR w planie zapytań XML, np.

0x9473FBCCBC01AFE

a CONVERT to BINARY daje zupełnie inną wartość

0x3078393437334642434342433031414645

Próbowałem zmienić definicję wartości w XQuery select na binarną, ale potem nie zwróciło żadnych wartości.

Jak wyodrębnić wartość 0x9473FBCCBC01AFEz planu zapytań XML jako VARBINARYzamiast, a nie VARCHAR?

Musisz użyć określonego stylu, jeśli chcesz zachować tę samą wartość binarną podczas konwersji z łańcucha. W przeciwnym razie SQL Server próbuje zakodować ciąg w taki sam sposób, jak by kodował 'bob'lub 'frank'.

To powiedziawszy, twój ciąg wejściowy nie wygląda poprawnie - brakuje albo jednego bajtu, albo jednego bajta za dużo. Działa to dobrze, jeśli upuszczę końcowy E:

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

Wynik jest binarny:

----------------
0x9473FBCCBC01AF

Jak wyodrębnić wartość 0x9473FBCCBC01AFE z planu zapytań XML jako VARBINARY zamiast VARCHAR?

Napotkałem coś takiego za pomocą HeidiSQL do kwerendy w tabelach CASD i rozwiązałem za pomocą fn_varbintohexstr () , jak poniżej:

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

W przypadku HeidiSQL wartość była niepoprawna, np. „0x3F3F3F3F3F3F3F3F” i stała się poprawna, jak „0x158B1DB75616484695684007CE98E21C”.

OBS: Działa od MSSQL 2008! Mam nadzieję, że to pomoże!