bit Obscene: SQL Server 2025 Developer Features
Video Summary
In this video, I delve into some of the new developer features in SQL Server 2025, discussing both the practical implications and potential pitfalls. We explore exciting additions like regular expression functions and JSON handling, which promise to streamline development but also come with their own set of considerations. The conversation touches on how these features might be misused—like invoking external REST endpoints in triggers or using them excessively in stored procedures—and the importance of understanding when such practices are advisable versus when they could lead to performance issues or other complications. We also touch upon Microsoft’s approach to documentation and guidance, highlighting that while they provide technical details, more emphasis is needed on practical advice to prevent misuse by developers.
Full Transcript
All right. So it turns out Sean is not going to be late for his interview to Valet Park Cars at the Olive Garden. So he’s able to join us for at least one more episode of the Bit Obscene radio podcast. In order to make Sean feel like a complete outcast, Joe and I have decided to formalize our attire as well. In this episode, oh, we have an and. We’re going to interrupt the intro, because Joe has something important to say. All right. I do. Sean’s man’s term it took so long to dress them. I was able to learn how to tie a tie and tie it before Sean got back. Yes. I wish I had that recorded. Joe nearly died twice. Both of you look amazing. Thank you. This is this is a lovely, I think, Paisley. Sort of. Yeah. Paisley tie. Paisley. All right. Cool. Yeah. Well, it’s a pattern. Okay. All right. Okay. Anyway, this continuing on the theme of SQL Server 2025 week, we’re going to talk about new developer features in SQL Server 2025. And apparently Joe is finally going to pick up the slack on something that is not a hangman’s knot around of a tie around his neck. And he’s going to talk about some of his favorite new features in SQL Server 2025. So take it away, Joe, now that you’re now you have now that you have now that you have to be a know one to interrupt. Hopefully you can think of something to say. I don’t know about favorite. You know, I was trying to find some new stuff to talk about. You know, it’s it’s a change event streaming didn’t just jump out. Like, you know, dig at the bottom of the barrel for some of this stuff. Yeah. No offense. I mean, I don’t take it. I hear that SQL Server 2025 reinvents the database developer. That’s that’s what I hear. Wow. It has been said. Wow. It has been said. But we’re I’m not gonna. I’m not I’m not ready to be reinvented. I feel like learning how to tie the ties enough reinventing for a while for me personally. So I’m gonna talk about the the more mundane things, you know, the kinds of things that the the ivory tower people with fake jobs used to these school that’s for database developers about where like we want to do like, regex and the database and they’d be like, ah, that’s using too much CPU SQL Server licensing costs. What kind of dumb ass would ever want to do regex where your data is? Obviously, you should send a billion rows to the clients and do the do the regex there and think about savings.
savings. Or I mean, like if you really think about it, like we only have one SQL Server. Well, that’s the way I think about things. One SQL Server at a time. All of our end users, they have phones and computers like think of all those untapped resources. You know, instead of doing the processing ourselves, we should just make people’s phones overheat. It’s almost like a cloud or Silicon Valley episode. Yeah. Um, with all that said, um, even if it’s perhaps not the best practice, it’s important to go where the people are. If you have, you know, 10,000 poor developers, all poorly re-implementing regex or fuzzy string matching in the database or CLR, if Microsoft can provide a built-in, which is better than, uh, hash flights, then I’m all for it.
And I mean, regex, right? It’s like, we have a few of those things in SQL Server 2025 regex, fuzzy string matching, something I’ve never used, but I’ve had to tell myself that that doesn’t sound like a lot of fun. Um, seemed like a rest endpoint kind of got to me.
Well, it’s funny that you say that because. Is it? Yeah, well, it is funny. Okay.
It’s funny because I’m reading the little bullet point right now for that. And it says you can have data processed via an Azure function using that, which I don’t, which I don’t doubt is true, but you can do the same thing for change tracking, which seems way more natural of a method. And only if you have change tracking enabled.
Well, you just have to enable it for, for, for the table you, I care about. I mean, like if the difference is I have a Q table, I enable change tracking, I inserted it in my Q table, and then I can call an Azure function on that row. Like that’s great.
I mean, compared to like create some goofy file or something, or. Did you, did you know if you enable change tracking and you use an availability group, your database has to restart an extra time? Yeah.
I did not know that is, but can’t you like not restart in the cloud? So it’s not. Nope. A problem. No, it’s just a different problem. Well, it sounds like Sean is advocating for SP invoke external rest endpoint, which is personally very surprising to me, but it’s nice to be surprised sometimes.
There’s also some stuff about JSON. I’m not really a JSON guy who has a JavaScript name, you know, like keep JSON where the JavaScript is inside of SQL Server. But I suppose I suppose I’m being one of those people who is, you know, the tut tutting that you’re not.
You’re not. You’re very English. You’re polishing the ivory on that. Yeah.
I have a question for you, Joe. I’m going to withdraw that one. Go ahead, Sean. Hit me. Yeah. You might be a little too young for this, but do you feel like, do you feel as though 2025 is 2005 all over again? I didn’t really work in 2005.
Okay. When I started the SQL Server, I believe we had to support SQL Server 2005 because we had a few customers are still on it. So we couldn’t use some of the wonderful 2008 features.
Like row numbers. Well, I was going to say, let me know. Like some of the, maybe all the window functions. Does anyone, my question is, do you remember SOAP endpoints?
Yeah. I remember SOAP endpoints. I do not. I was going to say, Eric, go ahead. Yeah. No, I don’t want to. Exactly.
I want to not SOAP endpoints anything. That’s, I’m good on that. We can, we can cut that right there. It feels very SOAP endpointy. I mean, Joe, I know you, I didn’t want to steal your thunder, but it, there are, you know, you were saying, you don’t want to be an advertiser telling people, you know, tut tut, and then you didn’t want to tell them.
I tut it. But at the same time, I think we all, at least on this call, understand that there are things to use and there are times to use them and narrow, very narrow times to use some of them. And then there’s what people do.
Mm-hmm. Yeah. The, the, the message about the narrowness doesn’t tend to make it too far past the screen. Right. It, it tends to get lost in transmission in a lot of places. And you’re like, you know, like I I’ve seen like, so like the SOAP endpoint thing never really hit me too hard because I didn’t really run into a lot of people doing stuff with that.
But what I got hit with on a bunch of stuff was the places that people would cram XP command shell calling external executable services, things like that. And like, I just feel like the external rest endpoint is just like, it’s okay to do that. It’s like it rubber stamps the whole brand of stuff that just people are going to do real bad.
Yeah. Yeah. Yeah. I’m looking at the announcement that says call rest graph QL endpoints.
Mm-hmm. Have data processing Azure function. Mm-hmm. Update a power BI dashboard. Apparently.
That’s an interesting one. Uh, call an on-premises rest endpoints. So do literally whatever you want. Yep. And you can talk to Azure open AI services. Those are, they’re like best, you know, at the front new use cases for this. Yeah.
Yeah. Yeah. It’s like, how else do you talk to your girlfriend? It’s impossible. The problem I have with the, uh, tut tutting is people who just like ended there, they don’t give like practical solutions and by practical solution, I don’t mean why don’t you just rewrite the whole application that someone else developed 20 years ago.
Like, I, I, I, I am all for telling developers, here’s a way to do something. Here’s some considerations. You’re supposed to be smart and well paid, figure out what the right solution is as opposed to just the, you know, well paid.
Yeah. Yeah. I wouldn’t want to send in a, a, a large comma separated list and split it in XML in my story procedure.
It’s not database appropriate. You need to rethink your entire design because who would ever want to do that? I mean, you know, you know, I mean, you, you are, you are just channeling Joe Silco right now and we all, we’re all here for it.
But, you know, I, I, I do agree that it is, it is, it is in impolitic to, uh, castigate people for doing certain things without offering secondary solutions. You know, it’s, you, you, you catch that in the SQL Server community quite a bit, especially with things like no lock hints.
Everyone loves to gang up on no lock hints, but no one really offers you a secondary solution for all of the terrible blocking that goes on under the default read committed garbage isolation level.
So I, I understand, I understand your position on this. And I, I think, uh, the, the, the issue for me with things like, uh, external endpoint invocation is it’s going like, and like, it’s funny cause like the regular expressions functions thing is like right above it.
Uh, and JSON is right under it. And so for, for me, what’s, what’s funny about it is that like the documentation is never going to tell you how not to use it. Uh, and this goes back just be like beyond like crazy, like features like this, this goes back to like, you know, other functions where it’s just like, like is null and coalesce and stuff where no one, nowhere in the documentation.
Is it like, Hey, this is a nice presentation there function for you to get rid of nulls with, uh, don’t maybe use this in your joints and where clauses. Cause it’s not going to go well.
Right. So you have like, like you, like all, all the, all the documentation does is like give you some of the technical details. It doesn’t say you probably shouldn’t do this with it. And, and I really wish that like, like, like a lot of blog blood is going to have to be spilled and telling people what they probably shouldn’t be doing with this and like where it’s going to hurt before any sort of message starts getting across.
So that’s, that’s my thing about it. I mean, that’s a real concern. And I, I know you all know I used to work in support. I have worked in support for quite a long time, but I used to work in support and I would, you know, try to help people with, with different things.
And, uh, one of the, I, I, this is an actual real thing. One of the items, uh, I got asked to do from an actual per, uh, you know, customer on SQL server was to document that you should not shut off your server in the middle of it running.
And that you should like, you should not pull the power plug to it. And that if you wanted it to talk to other servers on the network that we needed to document, that it actually have a network connection because none of the documentation says that it won’t.
Right. And so my only, my only thing there is there’s so many things, you know, you, you, you tell people, okay, Hey, you know, it’s, it’s a medical commercial. If you’re allergic to blank Brzenica, don’t take blank Brzenica.
Okay. Well, how do you know if you’re allergic to it? Yeah. You, you gotta, you gotta, we gotta say like, Hey, don’t divide by zero. Hey, don’t try to run invalid instructions.
Hey, don’t try it. Like, you know, there’s some things that are just, you gotta, you gotta take it to find out if you’re allergic to it, you know, but there are just some things that I think you should understand.
It’s a very bad idea. Yeah. Right. Like if, if someone says, Oh, a blocking function that I can call, this sounds great. Let’s call it in a tight loop for a billion items. Or let’s, let’s invoke an external rest endpoint in a trigger and see what happens.
Like every time we change data in the table, let’s go update our power BI dashboard. Yeah. Question for Joe.
How many triggers have you seen where someone has done something like that? Hmm. I’ve seen a trigger on a view that called a CLR, but that was a, which, which, which, then called in the Azure blob storage container.
But that is a temporary solution that we’re doing as part of a migration. Oh, so by, by I’ve seen it in a trigger, that means you wrote it. I didn’t write it.
Somebody wrote it. Don’t know who. Wasn’t me. I mean, if I had been asked to write it, I, I would have written it because it’s one of those, you know, this is a bad thing we’re doing. We’re doing it temporarily.
It’s, it’s gonna get us to a better place. So be it. Um, and, uh, to be fair, the old data is in. I actually have the name of this. It’s like the, it’s the future we use that like no one else uses the.
Stretch tables, uh, SQL Server data files in Azure blob storage. Oh, you. Oh, wow. Yeah.
So IO performance is very terrible. Yeah. So like going from that to, oh, well, we’re gonna. Call in the blob storage already using blobs. So whatever, uh, to Sean’s point. It does go the opposite way sometimes in that.
Yeah. Former job. We were complaining to Microsoft that he had, I think it was. He had two or more. Defined resource governor pools.
On big servers, you could see some really awful behavior. There was some kind of like memory thing. And I, I guess we asked so much about it. And we’re so annoying at that.
That, that someone said they were going to update all the documentation to say that it wasn’t supported to have more than one defined resource governor pool, which is a really interesting approach to take. Cause, uh, you know, like, I kind of feel like that should have been apparent from, from the beginning, but you know, it ended up not happening. Uh, cooler minds prevailed.
To what Eric said. I need to make sure I, uh, share the right screen here. It’s gonna be a real disaster. I could. Um, yeah, there we go.
Uh, age filter popping up. Podcast over. Yeah. All right. Good. I, I think when we, we, we won’t have to add that out. Um, broad impact potential. I feel like this is one of the, like a good examples where Microsoft actually is saying, Hey, maybe you shouldn’t use this in certain ways.
And here are some things to what, like career-term hints are best used for short-term fixes. So I feel like in career- Nothing is longer term than a short-term fix.
Like your, your, your thing in a trigger is forever now. Change this before 2012. Yep. Um, yeah. So speaking of a short-term fix, I’m, I’m blurry again.
You are. So just wave your, wave your hand in front of the camera. Like you just don’t care. Yeah.
Ah, there we go. Yeah. So, sorry, I got, I got you off topic though. What do you, what do you feel or how do you feel about Jason being another, now you have, you have XML, now you have Jason. Uh, people are already doing it.
If they’re already doing it and they’re not going to stop, you know, as we’ll make it a little bit easier. Yeah. Have you tried any of the functions with it? I’m, I’m not a Jason guy. Uh, I messed with it a little bit because I was curious if, um, uh, Jason, uh, like, I was curious if like the Jason indexes would, um, be responsive to page compression and they are, which I think is probably a good thing given how large the, the Jason stuff is.
Cause I mean, look, like, like, you know, like Joe said, um, people are already doing it and using it. And it’s, uh, it’s a real pain, uh, especially because people tend to use all this stuff relationally at some point. It’s like, you know, um, my, my, my feelings on Jason and XML are largely that, uh, it’s so like, it’s fine if you want to store that in the database, but then if you’re doing anything else with it in the database, you should probably reconsider.
Like make maybe occasionally just have to fetch it out. But, um, you know, I, I’ve, I’ve dealt with all sorts of applications where it’s like, we’re going to store this XML in a column. And now every time someone logs in, we’re going to parse this XML for like the user ID element and join on that.
And the server is going to crash whenever more than 20 people log in at one time. So like I’ve dealt with a lot of bad stuff as a result of being able to store these, uh, these blobby things in the database. So, you know, um, like really anything that can be done to make it less painful in the database is great, but, you know, um, developers being developers, uh, how well they keep up with these things and how well they use these things is yet to be seen.
Um, you know, I, you know, like, like, like the big thing with like, I mean, it was true with that. It was XML. It’s going to, but it’s more true with JSON because JSON is more popular with developers.
I think, uh, at least seemingly so, uh, is that the reason they’re in love with it is because there is no hard and fast schema rules for it. And so like, you know, the, the, the typical, you know, consult in the, you know, polish the ivory thing is like, like, it’s cool if you want to store the JSON there, but querying it in this way is going to suck. Like, if you want to do this, you should really parse it out to a relational table where you can, you know, have all the data separated out.
But developers love the sort of non-conforming schema aspect to it. And so they always want to like add stuff to it or maybe even occasionally take stuff out of it. So like even something like that isn’t always practical.
So, um, we’ll, we’ll see how well this stuff ends up getting used and we’ll see how well, um, developers end up treating, uh, JSON in the database. But, um, you know, my, my, my typical view is not too sunshiny on these things, especially given the, uh, very long lead times on people getting to new versions of SQL Server. You know, I think that goes to Joe’s point, right.
About the, you know, when he was talking about the regex, right, Joe, like it’s, you know, people are already using it. They’re already doing it. Yep. It might not be the best use. We, we might have to, or should there should maybe be some warning signs and something like that.
But if you’re already doing it, why not? As Joe was saying, maybe there’s less crap having 50 people do 80 implementations of it because they copied and pasted something off a stack overflow 16 times. And that’s what’s production code now.
Well, I mean, now, now it’s whatever the AI spits back is production code. So stack overflow is far, but, but I think, um, Joe, I had a question for you, you know, with the regular expression and fuzzy string matching. Do you think that that is going to offset a lot of the full text related queries?
Do you think that’ll hurt full text usage or full text usage was already pretty bad to begin with? It’s hard to hurt full text usage. It’s a tough, that’s a tough one.
My understanding of how full text is supposed to be used is you, you like, take a static database backup, you have a ton of data, and you build your full text indexes on that. Then you have your like researchers or whatever query that data, because it’s not, it’s not synchronous. So if you’re ever looking at some like, oh, I’m going to just build in a full text type of string search in my real time application.
As far as I know, there’s no way to make that synchronous. So you could get what could be perceived as wrong results. Like maybe that’s wrong.
I’m ill informed. It’s an asynchronous process. It has incremental. Yeah. Right. It does. But yeah. I think it could be great if, like I said, you have a static database. It’s not changing.
But other than that, it seems like that’s the only real use case. So I don’t have a lot of experience with it. It’s probably misused a lot.
So to your question, if this lets people misuse full text indexes less often, then great. I mean, it sounds like it’s going to be a little bit better, right? The fuzzy string matching.
I think we all have a friend, you know, who, who spells the word color incorrectly and color, behavior, labor, all sorts of things. Exactly. So I think there’s a lot of small areas where it’ll chip away, especially because full text really hasn’t had an upgrade in a while.
The. Well, what would be the point? Yeah.
Like who would, who would, who would benefit? Right. Make it a synchronous. Then finally we can, people like me can stop hating on it. I mean, but I think the performance around it is awful. It’s like, it’s good there.
But I think the, you know, the regular expression stuff, I’m not a personal fan of people using regular expression in the database. And mostly because when you start using regular expressions, you go from having one issue to a million issues, right? Especially the way that it works.
It’s not magic. I mean, I guess you can call it magic, but it’s not magic. And there’s a good. As far as I know, the implementation is done with one of the open source. I think Google’s open source regex model, but it’s, you know, it’s still requires memory, CPU, all those things.
Just like Eric was saying with the XML, like you might, you still may have to use DOM and the other stuff. Especially like the JSON stuff. Do you know off the top of your head?
Because just because I haven’t looked at it yet, but I know that like try parse and format are basically like, like CLR thin clients in a weird way. We’re like, like, like they, like they use CLR very heavily in their implementation. I don’t know if regex does this.
Like I have to look at regex is not CLR. No. Okay. I was going to say like, wow, that’s, that’s surprising. Yeah. That’s, that’s, that’s quite surprising, but yeah, cause I was, I was expecting to see like, like a query use, like with like, you know, regex is like, you know, used heavily in it or like using like, like, like a big where clause or something just to like rack up CLR time the way format does when you use it there. So, no, it should actually be pretty quick.
It should, it’s going to be native. It should be very quick. In fact, it should be faster than the CLR implementations that people have done. No. I mean, Hey, if, if, if you have the guy wearing a suit, tell you that, then it’s going to be a man, looking serious in a suit. It’s going to have regex all over the place.
Yeah. You know, and I think, I think the things that regex opens up are, are useful to developers generally. Um, you know, there are some check constraints that no amount of like car index, pad index, sub stringing you do can never, you know, get you what you want. Uh, and I do think that, you know, like, and it’s one of the, it’s one of those things where like clearly Microsoft is, has to make up messed up, has to like kiss and make up with a lot of customers in Azure who can’t use CLR for stuff.
Like, you know, like you can’t like Azure SQL database is like no CLR anywhere. So I, like, I do, like, I do realize that some of this stuff is kind of kiss and make up territory. Like we, like they have to build it into the product cause people want it.
So like want it so badly. But, um, I think, I think it is nice to have, I think what, um, people are going to do with it is going to hopefully, uh, keep me employed for a few more years. So, you know, Joe’s gonna, Joe’s gonna wave his finger at me again, because it’s, it’s, you know, one of the things you just said, there was a check and strength that no amount of, and I just thought to myself, why are you waiting till the data gets to the database to check it?
Joe Piazza- Can, uh, can’t, can’t trust the client, man. You know, untrustworthy those clients are these days. Joe Piazza- Because think about, think about people who bypass clients.
Um, like one of the, like, honestly, like one of the big, the big things that I see is you have like, and I mean, this is a somewhat related, but not quite related story was, um, one of my clients, uh, we were reviewing trigger code and they had sort of like the classic, like this trigger is designed to only handle one row at a time thing. Joe Piazza- Uh, and that was fine because the application guaranteed that only one row at a time would ever have a thing done to it. Joe Piazza- But they had all sorts of like admin users who would do whatever they wanted.
Joe Piazza- And so like they had lots and lots of rows in the table that were all messed up because the trigger only fired for the first row that they did something with. Joe Piazza- So like they would update like a 500 rows or something and only one of those rows would have a trigger result, like, you know, done with it. Joe Piazza- So like I, I, you know, there are things where you do have to protect the data where it lives.
Joe Piazza- And regex is potentially one of those things that can help keep lousy rotten data out of your database. Joe Piazza- That’s a way better example than the one I have. Joe Piazza- I was going to say some junior dev copying and pasting a generated code.
Joe Piazza- And, uh, do you think the AI is considering your, uh, your, uh, your data integrity? Joe Piazza- No, but I mean, it’s definitely not. But the, I guess the thing with that is why are you letting admins use like management studio to update stuff? Joe Piazza- It’s not me letting them like, Eric, why are you letting them look like, look, man, there, every company is going to have this stuff going on.
Joe Piazza- Like, like, like, look, we have big time, man. Joe Piazza- Look, uh, as much as I wish that everyone had a reasonable set of store procedures that everyone was required to use in order to interact with the database, that is just clearly not the situation at a lot of places. Joe Piazza- There are not that tight controls on things.
Joe Piazza- And, you know, like, I’m not saying it’s the wild west and at every, every single one of these places, but there’s a certain amount of like, well, this is a special situation. Joe Piazza- We’re gonna, you know, I don’t know, like, whatever. So, but these things happen like over the course of, I get it. Joe Piazza- I’m just playing devil’s advocate here of, you know, it’s the, we go from, as Joe called me out, we go from one end of like, you know, don’t, don’t do this and stuff to the other end of well, there’s all these special cases, but then to, well, we should know about all these special cases in the optimizer to well, maybe we shouldn’t, you know, everyone has the thing. So I get it. I’m just.
Joe Piazza- It’s not the same thing at all, man. You can make the optimizer better for everyone versus, it’s not the same thing at all. Joe Piazza- One thing that you guys could do is when you’re like creating a login or whatever, maybe have like a don’t allow this login to connect to the SMS, like built in thing. Joe Piazza- Just an option.
Joe Piazza- Yeah, no, I mean, really like something like that. Joe Piazza- That’s what logon triggers are for. Joe Piazza- Why do you have a login? Joe Piazza- Yeah, you can do it yourself when Microsoft can do it better. Joe Piazza- That’s true.
Joe Piazza- That’s true. Joe Piazza- Yeah. Joe Piazza- Because yeah, like in a lot of cases, you have these code deployment tools, they ask for high privileges. Joe Piazza- Mm-hmm. Joe Piazza- And now your deployment people have a high privilege login they can use, like unless you have login triggers like Eric said, or similar solution, like how are you going to keep people out? Joe Piazza- Yeah.
Joe Piazza- I mean, there’s actually, no, there’s actually a whole, a whole big topic. Joe Piazza- I don’t want to segue because we, that’s a whole big topic we talk about. Joe Piazza- Oh, that’s okay.
Joe Piazza- You know, we’re talking about developer features here. And we didn’t talk about any type of seamless access to secrets or other credential stores outside of, you know, EKM or, or AKV specific stuff. Joe Piazza- With the external rest endpoint invocation. Joe Piazza- That might be a valid use.
Joe Piazza- You might be the only person who knows what that means. Joe Piazza- I need you to consider that. Joe Piazza- The way I thought you were going was, you could like export data through that and that’s bad, but you seem to be saying the opposite. Joe Piazza- Well, no, what I’m saying is, you have, you’re saying, you know, now you have these high privilege users, right? And you don’t want to have these high privilege users.
Joe Piazza- And I think most people are pretty familiar with store procedures, your module signing, counter signing, stuff like that, you know, maybe not. Joe Piazza- I know one of the things that comes up a lot is, can I log, can I log in with a certificate, right? Joe Piazza- Can I just give this certificate and log in? And that’s not, I wish that were in there. It’s not in there.
Joe Piazza- It would have been great if it was in 2025, maybe, you know, hope for 2020 V next. Joe Piazza- But I think that’s one, one way to help with that, especially if you have systems that are pretty well bogged down, you can export certificates, etc, etc. Joe Piazza- Or if that certificate isn’t stored in some type of hardware storage module, right? AKA like an e-cam or something.
Joe Piazza- But, or that’d be an HSM, e-cams, the framework to get to the HSM. But the, you know, the REST API endpoint, I mean, could you not call, Joe Piazza- Something to get a credential stored somewhere and you need to be able, you know, you have to impersonate a different user or have a special login or do whatever. Yes. Joe Piazza- You can do that. And then is to your point, Joe, of like the data exfiltration. And this is definitely not an enable this and use it everywhere. But the, you know, the changes in data encryption, right? So you can have the Joe Piazza- Oh my gosh, I just had a stroke. Hold on.
Joe Piazza- Happens to the best of us. Joe Piazza- Yeah, there we go. All this stuff is too complicated for me. I like how they have a data ad and you can use big int. Joe Piazza- Yeah.
Joe Piazza- Like that’s the kind of meat and potatoes feature that makes me happy or a substring. Joe Piazza- Yeah. Joe Piazza- Where you don’t have to write some goofy code to figure out the length because you want like the whole string except you’re chopping off the front of it. Joe Piazza- Yeah.
Joe Piazza- All right, Sean, how is that? They’re still for enough time? Sean O’ The product function so you don’t have to remember all that log math. Joe Piazza- Yeah, yeah, right. The product function. That’s a good one. Joe Piazza- Yeah.
Joe Piazza- So one thing that was lost on me, and I’m sure we have viewers who are even younger than me, if you can believe that, is you seem to be alluding and referencing. Joe Piazza- Some soap endpoint crisis? Is it something that just got discontinued or like what was the big bad thing that happened with soap? Joe Piazza- It was blockchain all over again.
Joe Piazza- Oh, it just ended up being overhyped and useless and just taken out? Joe Piazza- So soap was the simple object access point or something like that. I forget, I’ll get roasted for it, it’s fine. Joe Piazza- Essentially, it’s a rest endpoint, but you get your object and some other information about it, and you can invoke it.
Joe Piazza- You can think of it as a rest endpoint, but it was another one of those things where that was the hot technology for the blockchain. Joe Piazza- It was the hot thing. It had to be in McDonald’s Happy Meals all the way to your toaster to every application everywhere. Instead of saying, how can we use AI? It was how can we use the blockchain? We need to be on the blockchain. So it was kind of that. It went to the wayside. There were other frameworks that ended up kind of coming in. And I also don’t think that this is, again, a very strong opinion that I hold. Joe Piazza- I don’t think you should be having a database server just with randomly punching holes for various endpoints that do whatever on them. And I know I say that as someone who loves HA, and we have a very specific endpoint for, but I don’t like, you know, now you have a, like, there’s nothing, for example, there’s nothing stopping anyone from creating another TCP endpoint for TDS. Right?
Joe Piazza- You can create 17 endpoints if you want. In fact, a lot of people don’t realize you can actually NUMA affinitize your endpoints. So yeah, I know exactly. I didn’t. It’s a very advanced setup. Joe Piazza- Yeah, I think a lot of people might never even think of that. Now you’ve ruined it. Joe Piazza- Yeah. I was rewatching Joe’s columnstore loading.
Joe Piazza- Oh, well, thank you. I appreciate that. Speaking of things people might not have thought of, I don’t know if you saw this, Sean, but about the endpoint thing. I had the brilliant slash horrible idea to, if you start up and extend it, you know, Joe Piazza- It’s really, it’s really hard to do. Joe Piazza- Yeah, I think that’s the kind of thing.
Joe Piazza- Yeah. Joe Piazza- Yeah. Joe Piazza- Yeah. Joe Piazza- And then, you know, I had the same thing, right? Joe Piazza- Yeah. Joe Piazza- Yeah. Joe Piazza- Yeah. Joe Piazza- So, like, it was already possible to just create, like, arbitrarily create files with the filenames you wanted in a blob container. Joe Piazza- And then, you can trigger based off of those files getting created using, like, Azure Functions or whatever else.
Joe Piazza- So I already had, like, a workaround to the, to the endpoint thing that you guys are officially supporting. Joe Piazza- And I was, I was gonna do it, too. Joe Piazza- I was gonna do it.
Joe Piazza- Believe me, I was- Joe Piazza- Just dastardly kids. Joe Piazza- Yeah, but then, now I don’t have to because, well, it’s funny because I, I got scolded by some guy and now you guys just make it easy to, you know, I was scolded is like not do it. Joe Piazza- I don’t know if the subject was like trying to get a like, like a PDF files or whatever on Azure SQL database.
Joe Piazza- What’s wrong with you, man? Joe Piazza- Yeah, right. Joe Piazza- Who’d ever want to do that?
Joe Piazza- And there are all these people, you know, the question got closed because who’d ever want to do that? Joe Piazza- And now you like look at the 2025 notes and it’s like, hey, you, you want to get some data, a SQL Server from anywhere you want? Joe Piazza- Yeah.
Joe Piazza- Or hey, you want to export some data? Joe Piazza- Oh, first one’s free. Joe Piazza- Anywhere you want? Joe Piazza- Yeah, with the first, first, first 100 megabytes are free, you can fit a lot of data. Joe Piazza- And export you long time.
Joe Piazza- 100 megabytes. Joe Piazza- Especially when you base 64 and code it with that. Joe Piazza- I’m sure that’s a really big deal for the people who need us. Joe Piazza- I’m not included in that group, but I’m sure.
Joe Piazza- Maybe Joe get rid of hash bytes, go to base 64 and code. Joe Piazza- Just make hash bytes scale. Joe Piazza- You don’t have to change anything. Joe Piazza- Just make it scale.
Joe Piazza- That’s it. Joe Piazza- I think you know, there was a time and place for that and it’s gone. Joe Piazza- Well, I’m the V positive and give you guys some credit. Joe Piazza- Because at time zone performance sucks on 2019.
Joe Piazza- But it’s like four times better in Azure SQL database. Joe Piazza- Just move to the cloud. Joe Piazza- Yeah.
Joe Piazza- That’s all it takes. Joe Piazza- Just sell your soul and move to the cloud. Joe Piazza- Well, you ended up putting on 2022 too. Joe Piazza- So for all you at time zone sufferers, there’s like the end of the title. Joe Piazza- I wanted to hit up just real quick.
Joe Piazza- So obviously there’s the new Chinese government coalition, which Eric had specified. Joe Piazza- But do you find, do you all find that I still in what I see and I want to get your thoughts. Joe Piazza- A lot of people are still mystified by coalitions.
Joe Piazza- Oh yeah. Joe Piazza- 100% I am often mystified by coalitions. Eric D’ I’ve never worked in a sophisticated enough environment where we changed the coalition. Joe Piazza- So I’m the wrong person to ask.
Joe Piazza- Like I know that there’s a lot written about them, but I find that my computer screen just like fuzzes over every time I try to read about them. Joe Piazza- The only thing that I have ever done to any effect with a coalition is use like Latin general bin to to speed up string stuff. Joe Piazza- Aside from that, I’m like, I’m like extended character special characters like like code page like sorting like I like, like I use whatever I just use the case sensitive version of whatever the default coalition for SQL Server is.
Joe Piazza- And I get hounded by certain New Zealanders who are like, that’s not a modern coalition. Joe Piazza- You should be using the SQL whatever one and I’m like, man, I like, like, okay, like, like, like, like, look, you’re right. Joe Piazza- But damn, I don’t know what I don’t know what else that’s gonna mess up for me.
Joe Piazza- I don’t know what it’s gonna fall apart in my life if I start using a new coalition. Joe Piazza- I have one client who has to use a Turkish coalition for one thing, because of where certain things come from that they sell. Joe Piazza- So that’s weird for me.
Joe Piazza- They love these names, huh? Joe Piazza- Yeah, so there’s a, I don’t know, there’s like weird swords in their alphabet and stuff. Joe Piazza- And so like, I don’t know. Joe Piazza- It’s all, it’s all strange. Joe Piazza- But yeah, I have no idea.
Joe Piazza- Like the UTF-8 stuff people people asked me about it. Joe Piazza- And I’m like, it’s kind of like, like Unicode and non Unicode had a baby and the baby had like seven fingers instead. Joe Piazza- It’s so bad.
Joe Piazza- It’s so weird. Joe Piazza- Yeah. Joe Piazza- Have you all heard of UCS2? Joe Piazza- No. Joe Piazza- So that’s what SQL Server typically uses for the encoding. Joe Piazza- Ah.
Joe Piazza- Which does support extended character sets, right? Joe Piazza- UTF-8 is interesting in that you have, if you look at the UTF spec, and I’m not an expert on the UTF spec, but if you look at it, there are all kinds of ways that you can have one character take up hundreds of bytes. Joe Piazza- UTF-8 is supposed to be essentially eight bytes, which is why it fits into a lot of places really nice.
Joe Piazza- Obviously it can be more, it can be less, but then you have UTF-16, UTF-32. Joe Piazza- Everything has one of those. Joe Piazza- Yeah.
Joe Piazza- But that’s what I was gonna ask, because like the binary correlations, I know a lot of people don’t like them for matching because they don’t get the case and sensitivity that most people typically want. Joe Piazza- But then they’ll say something like, well, it doesn’t sort right. Joe Piazza- I want to sort this one specific way.
Joe Piazza- Yeah. Joe Piazza- But I mean, look, they, SQL Server 2025 doesn’t add or fix any of those problems. Joe Piazza- So it’s like, you know, it’s just- Joe Piazza- No, but I was wondering about the correlations given the new one.
Joe Piazza- Oh, yeah. Joe Piazza- Oh, yeah. Joe Piazza- Well, I mean, look, I’m not gonna use a Chinese correlation, so I don’t know. Joe Piazza- This is America, Sean. Joe Piazza- Yeah, I do not have a use for a Chinese correlation at this point in my life. Joe Piazza- So I have no idea.
Joe Piazza- You know, I’m like, and I’m, when I look at like the new developer stuff in 2025, it’s like, like the double pipe string concatenation. Joe Piazza- I’m like, I’m sure that’s great for someone like, like somewhere out there, like some someone who loves Postgres is like I want like the double colon cast to a thing somewhere. Joe Piazza- And like, I don’t know, current date, fine. Okay, like, I’m sure that’s gonna really help people migrate from MySQL to SQL Server.
Joe Piazza- Like some of this stuff I don’t, I don’t fully understand like who the target audience is for some of these, like maybe like ORM developers or something, but it’s like a lot of it is sort of mystifying to me. Joe Piazza- And then, you know, like all like the distance and similarity functions and like, like, I know there’s someone out there who is like, finally, at long last, I have to be able to do that. Joe Piazza- I don’t know who the target audience is for it for that. Like, like sound decks and difference got us a very long way. Like why? Yeah, why are we here?
Joe Piazza- I think the fuzzy string definitely goes on top of that. I mean, preview features aside. Joe Piazza- Yep. But we’re going to talk about preview features next. I think we’ve we’ve killed SQL Server developer features. Joe Piazza- We ran, we ran through the entire list and we’re left wanting. So let’s let’s let’s nix this one here. So Joe can get his gas station pizza before it’s 10 o’clock at night. And we’ll we’ll we’ll do a potpourri episode for it for a short potpourri episode, so that everyone can yak about their their favorite and least favorite things. All right. Goodbye.
Joe Piazza- Bye. Joe Piazza-
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.