A Little About Working With Binary and Varbinary Data In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the world of binary strings in SQL Server, a topic that has been making its way to my top-performing blog posts on a regular basis. As someone who frequently deals with odd data structures like query plans and XML files, understanding how to correctly handle these binary values is crucial. I share practical examples from my work, including the nuances of converting strings to binary formats using `CONVERT` with specific cultures, and explain why simply casting or using `CAST` won’t cut it in this scenario. By walking through real-world issues like comparing query hashes and plan hashes, you’ll learn how to ensure accurate comparisons and avoid common pitfalls that can lead to incorrect results.
Full Transcript
Erik Darling here with, as the sign says, Darling Data. And as you know, signs never lie. Astrology signs, street signs, sign language, I don’t know, I guess you could count stuff like tea leaves and chicken bones and tarot cards and stuff in there too. Some of that slightly more open to interpretation, of course. So we’re not going to spend too much time on that. In today’s video, we are going to talk about binary strings. Why are we talking about this? Well, I’m going to be honest with you. Every month, or is it every month? God, it feels so often. I get these emails from Google because I have Google looking at my website and saying, this is the content that people find on your website. And one of the most popular blog posts that I have, which I don’t know, that says something maybe about my site, because, man, I have a lot of content on there that I find terribly interesting. But one of my top performing pages is about how to convert binary strings correctly in SQL Server. I’m not really sure how to take that. But here we go. Before we do that, and I don’t know, maybe I should start putting, I should start putting this in like, like random, like commando guerrilla warfare parts of the video so you just can’t skip over it. But I also try to make this a fun, lively and vibrant part of the video so that you stay tuned because who knows what I’ll say in these things. If you just love everything I do so much, if you just want to give me a big hug with money, you can sign up for a membership to the channel for four American dollars a month. And that’s a that’s a nice thing to do. Because the more people do that, the more the less in debt I am. So you know, that’s a great thing. If you are short for American dollars per month, for whatever reason, I don’t know, you’ve got a sick aunt and shkabogan, you can do other things to show your undying loyalty to darling data. You can like my videos, you can comment on my videos to make me feel like I’m not alone in this cold world. And of course, you can subscribe to the channel so that you get a notification along with like, I’m over 4600 other data darlings at this point.
So you can get notified whenever I drop drop this hot content on your heads. So those are those are other ways to do things. If you are in need of a SQL Server consultant, perhaps the kind of young and handsome consultant who can perform these rituals upon your SQL Server and heal it of its grievous wounds and injuries. I’m pretty good at all this stuff. And my rates are reasonable. If you would like high quality, high quality, low cost, there we go. Keep making sure I keep the highs and the lows in order there. On SQL Server, I offer all of the above beginner, intermediate and expert level training on things. God, my hand looks weird at that angle. It’s like long and knifey. You can get all of that training for life. Long live you. Long live you. For about 150 US dollars with the discount code spring cleaning, even though it is almost Halloween or getting into Halloween season. But a month away, I guess. It’s always spring at Darling Data.
If you would like to see me live and in person in about two months or actually, no, I guess just about a month now because it’s right after Halloween. Oh, boy. Time flies when you’re Erik Darling. You can catch me November 4th and 5th at Past Data Summit in Seattle, Washington. And as always, if there is an event near you that you’re like, God, why won’t Eric come to my town and talk to me about SQL or my weekend habits or, I don’t know, my fingernail collection, let me know what event is nearby you where we can talk about your fingernails and why you shouldn’t keep them. But with that out of the way, let’s talk about this SQL Server stuff, shall we? I think that’s a good idea. So I end up in my day to day life needing to pull data out of odd structures. By odd structures, I mean stuff inside a SQL Server that is usually stuff like query plans.
XML galore. The block process report, which more XML galore. The XML deadlock report, would you believe that’s even more XML? And it’s not on the list there, but the extended events, again, all XML. God, it’s just XML all the way down. Really, you would think that SQL Server was just like an XML document database, the rate that everything turns into XML in there. You may run into it elsewhere. You may find yourself importing XML files or JSON. I don’t know. Wave of the future. You might YAML. You might have another language that ends in ML or, sorry, another file format or specification that ends in ML that you need to mess with. And they all do strings weird.
The main issue that I find is when I need to compare the data that comes out of primarily XML structures, the stuff that I have to deal with is query related for the most part. So you have query hash and plan hash. Those are binary eight. And then you have SQL handle and plan handle. Those are var binary 64. So two entirely different beasts. And there’s, like, I know that there’s, like, some Microsoft posts with, like, shenanigans around, like, the stuff you can do with big ints.
I can’t always get that to work consistently. In some cases I can, in some cases I can’t. It’s, I don’t know. I just don’t understand what’s different in these things. Perhaps I am not XML enough. I don’t know. I’m not an XML expert enough to tell you. But I do want to show you some stuff that I’ve learned while dealing with binary strings.
That come out of various data sources. So if we look at this query, right, and we say, we want to find out if this string that came out of, let’s just pretend it came out of a query plan, is equal to this other binary value. SQL Server is going to say no. Right? That is not equal. That’s why we have a zero there. Remember, the logic for this query is if this equals this, then one else zero. So we do not equal this. So we get a zero back. Even if we do something like this, and we say, convert this string to var binary eight, we still get a zero back. Why? That is crazy.
We converted a string to var binary eight, or sorry, to binary eight. Why would it not match this thing? The answer is because when you convert strings in SQL Server that you want to say are binary or var binary, you have to use convert with one of the little culture things. There’s one conveniently right by my head there. There’s culture one and there’s culture two, but you can see that I’m using that same thing in the binary eight one that I’m going to show you now. So the difference here is that one does not attempt to add an OX to the beginning and two attempts to add an OX to the beginning. So depending on like what you’re dealing with, you might need to use one or the other. Right?
So like if we say, if we do these things, look at the different results that we get back. Right? So I want to show you all in one screen. If we just convert this string to binary eight, SQL Server does that like literally it says, Oh, this, this string is now, this is the binary eight representation of this string. All right. That, that, that, that, that is not what we want. What we want is SQL Server to preserve the binaryness of this string and actually treat this, this string as a bar binary value. We can do that with, with the OX using the, the one culture. And we can do that without the OX at the beginning, uh, using the, uh, the two, the two culture there. So if your string does not have an OX at the beginning, uh, that is one way to get that to happen.
The same thing goes for var binary of just about any length. I’m showing you what I get in here from, uh, like SQL and plan handles. It is the same deal. If you look at what we get from this one, this is not the binary that we have here. Again, this is SQL Server saying, Oh, you’re binary. Now you, you, you, you, you, we don’t care about you. We’re just going to turn you into something else. So the, the, the, the, the transmogrify you into some weird binary representation, right? Binary representation of what’s already a binary.
Big air quotes string. So if we were to change our query logic here to say, uh, convert, convert binary eight with the one on the end. Now we’re going to get that one. We’re going to get that match back. So if you need to do stuff like this with binary data that comes out of weird data sources, or if you’re getting a hankering to like, you know, pull data out of like, you know, XML for the plan cache block process report, deadlock report, or even, uh, some other extended event.
This is the kind of stuff that you have to do in my store procedures. When I’m like, this is from, these two are from quickie store. What I do is I pull the, I pull the value out, uh, as, um, as a string, but I have a computed column in my table that does the conversion over for me so that I make sure that I get the right value from whatever ends up in there. And this is the best way that I’ve found to do it. You can also do that with, um, with the regular, with, uh, sorry, with the, um, do I have two there? No, I didn’t, I just didn’t scroll down to the right one. You can also do that with SQL handles like this. So this one rather than just, this one converts to var binary 64 instead of var binary eight. Cause remember SQL and plan handles are var binary 64, uh, query hash and query plan hash are binary eight.
So that’s what I do when, when I’m pulling data, this is out for quickie store stuff in other parts of, um, my store, in other store procedures of mine. This is specifically from SP human events, uh, where, uh, I forget exactly which part this comes from. This might be from like the block process report stuff. What I’m doing specifically here is like notice for the first two that are binary eight, I’m doing the same thing where I’m getting that, like, you know, slash value slash text from the XML.
But for the top two, I could never get these to actually work like doing the excess hex binary thing, binary eight, they would always come back null or like an empty string or with the wrong value. So this is one of those things I was talking about, like this, this stuff can’t always work, but when I get it as a big int from the XML and I convert it as a, to var, to binary eight outside of that, then it works. Okay. So the big int to var binary eight and that case works everything else. No bueno.
But for the plan handle, which is var, var binary 64, I can do this neat little XML function trick with excess, excess colon hex binary. And that I can get to be var binary 64 natively. What’s the difference? Damn. Defino I look at this XML and it all looks the same to me. It just doesn’t work when I actually query it.
It’s strange. In SP Blitz Cache, there was a really, really, there was this weird bug in SQL Server up until like 2016 Service Pack something, two or two and a half or whatever, where it was like a plan hash or query hash values were becoming sort of corrupted in a weird way where they couldn’t like whatever was coming out of there was absolute junk. We were getting like wrong lengths and stuff. And this is what I had to do for, to get this to be right was I had to say convert binary eight, this bunch of zeros.
And then I have the right function there. And then I get a substring of the query hash cutting off the OX, just getting everything after the OX. And then I get, uh, well, sorry, that’s like the three to the 18th thing with this, this thing concatenated on and then convert that to a binary with the two culture. So that was a really nasty thing that was happening in there. As far as I know, I still can’t, I still couldn’t pull that out of SP Blitz Cache just because someone might be on a 2016 version that still has that bug.
So there, there, it’s going to stay with this confusing code, just trying to get a query hash and a plan hash to work. So be careful out there. If you need to work with a binary or var binary data, uh, how you handle it and convert, you basically can’t use cast for this because cast doesn’t allow you to supply a culture where you can tell, uh, like with convert, you can tell it how to treat the string with cast. Cast is usually a kind of crappy way of doing things. Um, I do kind of look forward to the day when Microsoft adds like the double colon cast that like Postgres and other databases have, but I don’t think that would work here.
I don’t think that would fix this either. I think you pretty much straight up need convert for this one. So, uh, be very careful out there with, with, uh, how you treat your binary data, because if you need to compare it to other binary data, it could be awfully, terribly, disastrously, and most importantly, incorrect. So, thank you for watching. Big chin. I hope you enjoyed yourselves. I hope you learned something. Uh, I hope that, uh, this video was of some use and utility to you. Otherwise, I’ll just go jump out the window. The last thing you’ll hear is the sound of shattering glass and then there’ll just be empty space on the screen for until my camera overheats, which takes about 40 minutes.
So, anyway, uh, I think, I, I think I hear Ma ringing the dinner bell. So I’m going to go, gonna go stuff myself full of chicken and broccoli. Uh, that’s what, that’s what, that’s what we do here at Darling Data. We, we stay healthy. Uh, anyway, thank you for watching. Uh, goodbye. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.