This is a short post that I wanted to write on the heels of doing a bunch of work in sp_QuickieStore.
Many times, pulling data out of odd structures like XML or JSON can lead to difficulty in correctly typing each output element. I run into this commonly with query plan XML, of course. You may run into it elsewhere.
The main issue is that I often need to compare what comes out of those odd data structures to data stored more properly in other system views. For example:
- Query Hash: Binary 8
- Query Plan Hash: Binary 8
- SQL Handle: Varbinary 64
- Plan Handle: Varbinary 64
There’s some shenanigans you can use around big ints, but I’ve run into a lot of bugs with that. I don’t want to talk about it.
As an example, this won’t match:
SELECT c = CASE WHEN '0x1AB614B461F4D769' = 0x1AB614B461F4D769 THEN 1 ELSE 0 END;
The string does not implicitly convert to the binary 8 value. The same is true when you use varbinary values.
You might think that just converting the string to binary 8 would be enough, but no! This will still return a zero.
SELECT c = CASE WHEN CONVERT(binary(8), '0x1AB614B461F4D769') = 0x1AB614B461F4D769 THEN 1 ELSE 0 END;
In this case (ha ha ha), you need to use the additional culture parameter to make this work.
SELECT no = CONVERT(binary(8), '0x1AB614B461F4D769'), yes = CONVERT(binary(8), '0x1AB614B461F4D769', 1);
no yes 0x3078314142363134 0x1AB614B461F4D769
The same is true with varbinary, too:
SELECT no = CONVERT(varbinary(64), '0x0900F46AC89E66DF744C8A0AD4FD3D3306B90000000000000000000000000000000000000000000000000000'), yes = CONVERT(varbinary(64), '0x0900F46AC89E66DF744C8A0AD4FD3D3306B90000000000000000000000000000000000000000000000000000', 1);
no 0x30783039303046343641433839453636444637343443384130414434464433443333303642393030303030303030303030303030303030303030303030303030 yes 0x0900F46AC89E66DF744C8A0AD4FD3D3306B90000000000000000000000000000000000000000000000000000
The real answer here is to not rely on conversions, implicit or otherwise, when comparing data.
But, if you ever find yourself having to deal with some wonky binary data, this is one way to get yourself out of a scrape.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.