In this video, I delve into the intriguing world of large object (LOB) data and its impact on locking in SQL Server. Erik Darling from Darling Data shares his insights by demonstrating how incorporating LOB columns like text or ntext can significantly increase locking problems, particularly through examples involving exclusive and shared locks. By exploring a specific update query that targets a single row with a LOB column, I illustrate the complexities of lock management and highlight the importance of proper indexing to mitigate these issues. This discussion is not just theoretical; it’s practical advice for anyone dealing with large data types in their SQL Server databases.
Full Transcript
Erik Darling here with Darling Data, feeling the spirit of the season upon me. And what I want to give you is the gift of lob data. And tell you a little bit about how it can increase locking problems in SQL Server. Shocking, I know. Down in the video description, you will find all sorts of helpful resources to give me money. If you want to hire me as a consultant to make your SQL Server faster, you can do that. If you want to purchase my training material, let’s say you want to learn all the things that I know, and you want to go forth into the world and tune SQL Server on your own, you can do that. For a very reasonable price. You can also support this YouTube channel, Ask Me Office Hours Questions, and click on the requisite buttons to like and subscribe and share with friends all of this wonderful, magnificent, free content. content about SQL Server. Isn’t that just grand? The spirit of the seasons. The spirit of the season is upon us, my friends. Once the spirit of the season leaves us, and the new year begins, and the earth, at least in this hemisphere, begins to thaw, I will be leaving the house once again, like the spring flower that I am. I will be at Datatune Nashville, March 6th and 7th.
And I will be at Data Saturday Chicago. So much data. So many datas. So little time. March 13th and 14th. I will have advanced T-SQL pre-cons at both of those. And I would suggest that you come to those if you want to learn some crazy T-SQL stuff. Because that’s what I do. That’s what I do. That’s what I’m into. That’s my fetish. Crazy stuff. Anyway, let’s talk about lobs and locking. Now, over here, we have an update query. And this update query will seek to update a single row in the post table, where the ID, which is the clustered primary key of the post table, equals 1169.
All right. And we’re going to use my table valued function, what’s up locks, which is available at my GitHub repo. It’s at code.erikdarling.com. I actually had to do a big rewrite of this before this because nothing was wrong with it normally. But for this demo, performance was not good. But we can save that for another day. So let’s go update that single row. And let’s look at how involving lob data in a query makes for some interesting locks.
Now, if we look down in the results of what’s up locks, we will see that request session ID 57, that is this SPID, right? That is us here. It has some intent exclusive locks. But more importantly, it has this exclusive lock at the key level. And it has just one of them, right? So there’s just one total row lock here, right?
So what we’re going to do now is come over to this window and look at when some different queries get blocked up here. So if I select the top 100 rows from the post table, where ID is greater than 900, this will actually start returning rows. All right. But this will get stuck at a certain point. Like if we scroll way down in the results, we have returned up to row, we have returned 90 rows and we have stopped at 1149.
But now we are blocked. And if we come look at the locks that my query is attempting to take, we will see that this one shared lock is being blocked, right? This is the one that’s waiting, right? So we do not, we are not able to get this lock. So this query was able to read up to a certain point until it hit this one lock and now it is stuck, right? Because we have not committed that other transaction.
So this one gets stuck there, right? Let’s say that now we want to order by score, right? So now we are ordering by the score column. Things will get blocked in a far different way, right? This one does not even start returning results. And part of the reason why this one does not start returning results and part of the reason why I had to tune WhatsApp locks pretty heavily this morning and still add a bunch of query hints to it so that the optimizer stopped doing, gosh, dumb things all over the place.
Sorts, stream aggregates, merge joins. It was a bad scene, man. This thing was taking like 40 seconds to run. I got it down to about five seconds, which is like, like glory, hallelujah. It’s a Christmas miracle. How dare you? But look what happens now. This is the important part, right?
It’s like we have this, this thing down here that’s waiting, right? But look at all of the lob locks that have been taken, right? All these shared locks. We now have 417,000 Hobbit lock counts, heap or B tree locks.
And that, that results in, well, I mean, 4,000, 4, 417,000. Wait, is that 417,721? Oh, oh my. That’s a seven digit number. 4.1 million page locks. And this is because SQL Server is now has to follow all of the sort of the lob pages for the body column. All right. And we can, we can prove that’s true because if we, if we change the query a bit, right?
If we cancel this thing, cause that’s going nowhere and we say, select the top 100 and we, we leave the body column up. We’re just going to select the store, the score column. Now, if we run this, this gets blocked like in the, in the same way, but we don’t take that for those 4.1 million locks in here, right?
There’s no 4.1 million number in that one. So, so just selecting that lob data will contribute to additional like locking stuff in shared locks needing to happen. Now that would also happen if we just had body in the where clause. If we said where body is like, you know, just a single character, just find me any body, any body will do.
This thing will go back to taking like five seconds again. And we end up, well, this time we end up with, let’s see, 1720804. So still a seven digit number of 1.7 million heap or B tree lock counts. So just involving lob columns anywhere in your queries can lead to increase locking requirements and increased locks and other unpleasantness, probably blocking and some other stuff.
We could of course, uh, ameliorate. I think that’s, I think that’s the appropriate word for what would happen here. We could solve the problem. I, I ought to just speak plainly sometimes. Uh, if we had an index, uh, like this, that we are, we could return the data.
We would have far fewer problems going through things. Um, so like we would, like we would have the, like be able to sort the data by score and then find whatever IDs we care about. Everything would be fine. We wouldn’t even necessarily need to have ID as a second key column here because it is the clustered primary key.
And it would end up there anyway, if we just had a single key column on score. But then someone, someone might come along and fiddle with our index and add other columns in here and screw us all up. So sometimes it’s best to be explicit about, uh, the, the order of columns when we have even a clustered primary key involved.
And we want that to be where it is for a reason. Then we could maybe add other columns over here. Like, I don’t know, accepted answer ID without messing up the order of the initial keys that we, we want in there.
Anyway, strings were a mistake. Big strings were an even bigger mistake. And as much as possible, you should avoid max data types in the database.
There are a bevy and variety of reasons for, uh, for avoiding them. Uh, and I’ve talked about many of them, but this is, this is yet another one. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you maybe tomorrow’s video. I don’t know. I might, I might, I might take some time off for Christmas, New Year’s and stuff. We’ll see how it goes.
No promises. But if, if there, if there is a video tomorrow, I will see you in it. All right. So, thank you for watching. Thank you for watching. We’ll see you next time.
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.
Query Store Hint and Plan Guide Annoyances in SQL Server
Video Summary
In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.
Full Transcript
Erik Darling here with Darling Data. And today we’re going to talk about some of my annoyances with query store hints and plan guides. If you have a problem with that, you can leave. Before you leave, before I talk about that, there are some links down in the video description. If you want to hire me as a consultant to make your SQL servers faster, in exchange for money, you can do that thing. You can also buy my training if you want to try to do that on your own, learning from me, because I’m good at it. You can also support this YouTube channel because, I don’t know, I mean, I guess the camera’s already paid off, but, you know, these ring lights, electricity ain’t getting any cheaper, right? Something like that. If you want to ask me office hours questions, I do that every Monday. I answer five of them, as long as there are five to answer. If there are five to answer. If there are not, I sit in a corner and cry and wonder why no one likes me. But, anyway, I’ll be leaving the house starting in March of 2026, specifically going to Nashville and Chicago, one weekend after the other. Data Tune Nashville and Data Saturday, Chicago. Good times ahead. I will be teaching advanced T-SQL pre-cons at both of those.
So, buy those tickets now, because they don’t last forever, kids. Didn’t always have it this good, did we? Couldn’t just always leave the house and go get some SQL Server training outside. I don’t know, right? Anyway, let’s talk about why these things are annoying. So, that’s not what we needed. We needed Management Studio, not Hyper-V. Hyper-V is an unserious technology for consultants like me. Anyway, let’s truncate this table called bad idea, and let’s stick five rows in the bad idea. And so, what I want to show you here is, I mean, it’s just kind of a funny hint, right? This no wait hint. Under normal circumstances, no wait won’t do anything, right?
But, like, if we, like, say, start a transaction, and we try to insert a row into this table, we don’t close the transaction. Then no wait does kind of a funny thing. It actually just does, like, what a lock, like, the lock timeout thing does just immediately, right? I mean, it still gives us, like, the first five rows, but then on the sixth row, it bails, right? It’s like, ah, there was a lock. I’m gone, right?
So, it doesn’t return results. We could select, sorry, we can’t select ID6, because ID6 is the row that we have locked for the insert, but we could select ID5. Why we have this hint, well, I mean, I don’t really know or care all that much, but we’re here to talk about plan guide annoyances. Now, let’s say that we wanted to get rid of this hint. Let’s say that this hint was ruining our lives.
Let’s say that this hint were completely destroying everything that we believed in as far as SQL Server goes. We’re like, why does this query just, like, just die the second it encounters a lock? Is that, should we be doing that? Is that correct?
Like, maybe we should use lock timeout, make it wait a little bit longer. But, so, Query Store has the ability to add hints to queries via the option mechanism. I don’t know why ZoomIt is being difficult, having some trouble starting up. Apparently, it’s cold.
But, if we were to try to add a table hint, the way that we can add a table hint with plan guides, well, Query Store gives us a rather infuriating error. Query Store says, no, you cannot set query hints, or rather, setting query hints table hint in Query Store is not supported.
Query Store says, no, you cannot set query hints. Well, Microsoft didn’t think very hard about this one, did they? I mean, they thought hard enough to make an error message, but what a giant gap in capability.
Query Store says, no, you cannot set query hints. Someday, competent people who care will work on SQL Server again, I’m sure of it. We don’t know when, don’t know how, but someday it’ll happen for us.
Now, with plan guides, you can supply table hints, but plan guides also have a rather annoying thing with them. Where, if we tried to create a plan guide like this, and the cool thing about like table hints is you can use them to ignore a lot of stuff. Like, if there was like an index hint on this thing, and you were like, wow, that index hint is stupid, you could use a table hint like this, and just point the table hint to the alias for the table, and that index hint would effectively be ignored for the query.
But we can’t do that with like walking hints, right? If we try to run this, SQL Server will say, well, let’s put this in a way that is a little bit more easy to digest when I zoom in. Let’s see.
Cannot execute query. Semantic affecting hint no weight appears in the with clause of the object bi, but not in the corresponding table hint clause. Change the option table hints clause so the semantic affecting hints match the with clause.
Now, I might very well be using a plan guide specifically because I want to override that semantic. Maybe I hate that semantic. Maybe that semantic is hurting things.
Maybe I want to get rid of it. I wish that this thing had like the PowerShell like dash force parameter for it. Because it’s like, no, I specifically want this to not happen.
I’m trying to make this not happen. Why are you making me do this? So that’s my annoyance there.
With query store, it’s just like, no, no table hints whatsoever. With plan guides, it’s like, no, but you need to keep this thing because we say so. And you’re like, no, I want to get rid of this thing.
This thing is actively hurting me. Now, like, you could do that with a query like this. And you could, like, if there was an index hint, you could say, hey, like, let’s ignore that index hint.
That is completely successful. And, of course, we could get this to work with the table hint down here. But it would completely undo what we’re trying to do, which is not have the no wait hint on here.
And this will happen with, like, lots of dumb semantic affecting hints because SQL Server is like, oh, no, the semantics. But you’re like, I want to get rid of the semantics. But you’re making me keep these semantics that I don’t want.
So it would be really nice if with, you know, query store, we had the ability to supply table hints because what kind of half-baked situation is it that we don’t have that? And with plan guides, it would be nice if you could say, no, plan guide, I really do want to override that semantic affecting hint because I don’t like it. I think it’s bad and I don’t want it there.
So that’s that. Neither one of these things exactly, neither one of these plan forcing features exactly covered itself in glory here. Query store, of course, with the completely half-baked query hints and plan guides with not allowing me to get rid of the semantic affecting hint that I intended to get rid of with a plan guide.
So thanks for both of those. Nice Christmas presents. All right.
Happy Merry. Thanks for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where I think we’re going to talk about how Wob data makes for some weird locking. And we’ll have a lot of fun with that, won’t we?
All right. Thank you for watching. Thank you. Thank you.
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.
The missing index DMVs keep recommending the same index with 15+ included columns that would basically duplicate our clustered index width. The equality columns make sense, but I’m suspicious about blindly adding all those includes. How do you evaluate whether a wide covering index is actually better than a narrower index with key lookups, especially for an OLTP workload?
I thought table variables were immune to parameter sniffing issues because they’re not parameterized, but I’m seeing wildly different performance for the same stored procedure depending on the data volume in a table variable we populate. The cardinality estimate is always 1 row regardless. Is there a way to get better estimates for table variables, or should I just rewrite everything to use temp tables?
We’re getting 1205 deadlocks on SELECT queries that don’t modify data. The deadlock graphs show intra-query parallelism exchanges as the resources. These are reporting queries with MAXDOP 8 hitting a columnstore index. Why would read-only parallel queries deadlock with themselves, and what’s the best way to prevent this without just setting MAXDOP 1?
I’m getting sort warnings in execution plans even though the memory grant is 10x larger than what the sort actually needs according to the plan properties. The ‘GrantedMemory’ is 500MB, the sort only uses 50MB, but I still see a sort warning. I’ve ruled out parallelism issues – this happens even with MAXDOP 1. What causes sort warnings when there’s obviously enough memory?
I created a filtered index on WHERE StatusId = 2 for a common query pattern, and the index is way smaller than the full table index. But the optimizer still chooses the full table index with a residual predicate instead of using my filtered index. I’ve updated statistics, rebuilt the index, even added query hints – nothing works. What are the gotchas that prevent filtered indexes from being selected, even when they seem perfect for the query?
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.
In this video, I introduce a new course titled “Get AI Ready with Erik,” designed to help you navigate the upcoming AI features in SQL Server 2025 and beyond. Given that Microsoft is positioning SQL Server as AI-ready, it’s crucial for professionals like us to be prepared to work with these new capabilities. The course focuses on practical applications rather than deep theoretical knowledge, teaching you how to leverage vector search effectively without getting bogged down by the complexities of underlying mechanisms. We cover everything from foundational concepts to advanced topics such as hybrid search and multi-vector search, ensuring that you can make informed decisions about when and how to implement these features in your production environment.
Full Transcript
It’s Friday and we’re introducing a new course. It’s called Get AI Ready with Erik because Microsoft says SQL Server 2025 is AI ready and you need to be ready to work with AI in SQL Server. Whether you care about it or not, whether you think it’s good or bad or not, at some point you’re going to have to deal with these new features and I want you to do it in the least stupid way possible. The whole foundation of the course is based on this premise. You don’t need to know how it works. You don’t need to know all the crazy math. You don’t need to know all the weird theory. You don’t need to know all the, you know, gears and whistles and bells and stuff that gives people six fingers on the, on the backside. You just need to know how to work with these features in smart ways. That’s what I want to teach you. The whole thing started because I do SQL Server performance tuning and I saw this whole thing, the whole suite of new things hit SQL Server and I said, gosh, the way things are going, I’m going to be a little bit more. I’m probably going to have to do some performance tuning work on this. And it turned out on my path to learning how to do performance tuning work on this. I had to learn a whole lot about how all the stuff worked inside. So that’s my distillation here, how all this stuff works. Now you can work with it too. Vector search is fairly easy to grasp as a concept. It stores these embeddings, which are a series of floats, which might, I guess, store it as a JSON. And then when you change other text into a similar, embedding another array of numbers, vector search can tell you how close, how similar those strings are without a bunch of nasty wildcard searches and stuff, right? No longer just like saying where something is like, you know, percent word, percent word, percent word, or like, you know, just long strings of or clauses doing this stuff. It allows you to sort of search by meaning and intent, not just like, you know, normal keyword searches. So you can find similar things pretty fast, even on pretty big tables.
It’s not perfect though. Details often get lost. And sometimes you still need to do some keyword searching and sometimes you still need quality filters on things to make sure that, you know, not only is the content that you’re providing in your search is relevant, but also that it’s not just like garbage, right? So, you know, that’s no fun. Microsoft is, you know, Microsoft is, you know, of course gets a late pass on this one. That’s, you know, behind the curve a bit on this. Most other, you know, of their competing databases have had this stuff around for a while, but, you know, moving data to another database, as I’ve learned from seeing so many people talk about how they’ll be in Postgres in three to six months and they never seem to get there. Your data is already in SQL Server, moving it off to either a specialized database or a whole other data platform just to take care of vector search requirements is probably a non-starter for a lot of people.
You’ve got everything already in SQL Server. Adding a vector column or a table with a vector column in it beats migrating for most people in the world. Microsoft was late, but, you know, they actually showed up. You have some generally available features available right now and you have a lot more features still in preview.
Will they ever be real? I don’t know. Will Microsoft ever finish anything again in SQL Server? I have no clue, but I can tell you what’s there and what’s not. So this is what the course covers. It’s in eight parts over around 28 videos. It’s about 10, 12 hours of total content.
We cover foundations, practical applications, hybrid search, vector indexes, native integration, managing embeddings, production patterns for dealing with all this new vector stuff. And then some more advanced topics like rag and multi-vector search. It’s a pretty solid course, I think.
And one thing that I want to say here is that, you know, where a lot of this stuff is in preview and like maybe isn’t out yet in SQL Server 2025 is RTM. Like we don’t even have one cumulative update for it as of this recording. I’m going to be adding in stuff as cumulative updates add, you know, substantive changes and new stuff to SQL Server.
So this isn’t like a course that’s just going to rot on the vine. As Microsoft works on this, the course will get updated alongside it. So there will be new content to cover new things as the features move along.
What I hope you get out of this is understanding when vector search makes sense. Because when your boss comes along and says something like, hey, we want to do this and here’s what we want to do it for, you can speak intelligently about it. You have some background.
You have some knowledge. You have information. You can feel empowered to speak authoritatively on the subject. Right. We want to know what’s production ready versus what’s still in preview today, how to monitor and troubleshoot performance issues and sort of like capacity planning and all the other stuff that goes along with, you know, DBA and developer tasks. And we want to know how we can implement this in production and make working with the current set of limitations as pain free as possible.
So right now, if you go to training.erikdarling.com, there should be a link down in the video description directly to this course as well. You can use the coupon code AIREADY to knock $100 off the cost of the course. That coupon code is available for the first 100 people who sign up.
So if you want to be one of the lucky hundred to save $100, I suggest you get moving because things are already flying off the shelves and, you know, I would hate to see you miss out on this wonderful opportunity. Anyway, thank you for watching. I hope you enjoy the course and I will see you, well, when I see you.
All right. Goodbye.
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.
In this video, I delve into the often maligned `MERGE` statement in SQL Server, defending it against its critics and highlighting its unique benefits. You might be surprised to learn that `MERGE` can actually help catch non-deterministic updates—something a standard `UPDATE` query won’t do. By demonstrating how `MERGE` issues a warning when you attempt a non-deterministic update, I aim to show why this statement deserves more respect in your database toolkit. The video also covers practical solutions for making these updates deterministic using window functions like `ROW_NUMBER()`, ensuring that the data integrity and consistency you expect from SQL Server are maintained. Whether you’re looking to improve query reliability or just want to understand a lesser-used feature better, there’s something here for everyone who deals with complex data operations in SQL Server.
Full Transcript
It may be very little surprise to you. I am Erik Darling with Darling Data. But what may surprise you, may rock you to your very core, throughout your soul, is today I am going to defend merge statement in SQL Server. I know, I know, no one will ever defend it. Right? People will hate on it and other people will say, it’s not that bad. No one ever defend it. I know, no one ever sticks up for merge. I’m going to stick up for merge today. Because, uh, I think there’s a really interesting thing that merge will do, if you write a non-deterministic update, that a normal update will not do. Maybe it should, because shouldn’t everything in a database be deterministic? Doesn’t, does ACID mean nothing to you people? What’s wrong with you? Down in the video description, which is my favorite part of the video, there’s all sorts of helpful links. Like, uh, ones where you can hire me for consulting, buy my training, become a supporting member of the channel for as little as $4 a month. You too can, uh, I don’t even know what, what that would do. But it’s not about what $4 a month does, like, like on its own. It’s what $4 a month does from all of you in the aggregate. Right? That’s, that’s where things get special and interesting. So, gang up on me. Uh, you can also ask me office hours questions, because I like answering questions.
Especially good questions. If you have a good question, that’s even better. I’ll give you a high five on that one. And of course, as usual, please do, uh, like, subscribe, uh, tell, tell your mom about my YouTube channel. She’ll probably dig it. Uh, I’m a, I’m a, I’m a likable fella. Uh, you know, sometimes, uh, um, you should drink when I do these. Anyway, uh, I’ll be at a couple of events coming up in March, which is, you know, a few months. Two months off at this point. I guess like four months off at this point. But hey, um, that gives you, buy a ticket now. So, you know, it’s like verge of selling out probably at this point. So might as well get in there before, like all the butts are in the seats and one of, none of those butts are yours. Right? So, uh, data tune in Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. I’ll be doing pre-cons at those advanced T-SQL. You know, the stuff that will blow your mind. Um, it won’t melt your brain. I hate when people say melt your brain. I think that that’s what the kids call cringe, but you are, you can’t, you can learn a lot from a dummy. So you should, should probably do that. Anyway, we are feeling festive today, ain’t we?
Okay. So let’s, let’s go defend merge. So, uh, I’m going to make a table here, a temp table. Temp table is not the problem. The temp table is just fine. Uh, but the problem becomes when we try to update this temp table, right? So we’re just going to put one row in there because all we need to prove this theory is one single row. Right? Uh, and we’re going to use John Skeet cause John Skeet’s just real easy to pick on stack overflow. John Skeet causes, I mean, for his, I’m going to say he causes a lot of query problems, right? Everything John Skeet gets involved, everything blows up. So the thing here, oh, you know what? I’m in the wrong database. We gotta move that over. There we go. Now it makes more sense. Life is, life is grand now. Right? So let’s, uh, let’s insert that one row again. Oh, professional. And, uh, so the, the point here is, uh, if we turn on query plans and we look at the plan for this query, uh, we will see that this query produces a parallel execution plan. Right? And by parallel execution plan, I mean, it, it looks like this, right? Where there’s, there’s parallelism. So great. Right? Uh, everything went parallel and everyone was happy. So that’s, that’s, that’s one of the issues that comes up here. And the, what that issue contributes to is that if we try to update this repeatedly, right?
If we run this update multiple times, SQL Server will update our table with multiple, with different rows on almost every execution. Right? So if we run this pair of queries together, and wait a second for it to run, it does some stuff. Uh, we get post ID. Oh, let’s do our special zoom here. We get post ID 156426. And if we run this again, the amount of time this takes is better work. Uh, there we go. 157291.
And if we run at a third time, I bet that you’re going to be surprised. We get 148352. This is a non-deterministic update. And by non-deterministic, I mean, uh, we don’t know what row is going to end up. As the update values, right? Now, how I’m here to defend merge and why I’m here to defend merge is because merge, unlike the update from center, unlike the update syntax, merge will, will, will, will warn you about these things.
Merge will say, Hey pal, uh, something’s a muck here, right? So we’re going to merge into our temp table, and we are going to use our select query as the source. And we’re going to say when matched, uh, that didn’t go well. We’re going to say when matched and exists, uh, a difference between the sets of columns in here, then we will update the table with, with those, those columns. And if we run this, let me wait a second or another second. There we go. Look, it happened. Oh, oh, merge.
Thank you. Thank you for caring about acid merge, but you could have been a little bit, maybe pithier with this error that we got here. The merge statement attempted to update or delete the same row more than once. This happens when a target row matches more than one source row. A merge statement cannot update, delete the same row of the target table multiple times. Refine the on clause to ensure a target row matches at most one source row or use the group by clause to group the source rows. Oh, merge. Thank you. I mean, you could have just rewritten the whole query for us by the time you spit that book out. But here we are, here we are. And I mean, sure we can, you know, maybe it’s a sign that we could aggregate some stuff or we should have done things differently, but at least it’s nice that merge tells us like, Hey dummy, something’s not gonna, something’s gonna look weird if you run, if you keep doing this, like you you’re screwing up. Right? So the way that we can fix this and we can, we can use merge here for this is what we can do is we can use row number, the row number function to make sure that we limit this to one row. Right? And, and something that is particularly important here is that we do not rely on what is essentially a non-unique column, right? Because we could have, we could have multiple last activity dates, right? Like the same. We, this is not guaranteed to be unique, that we have a unique tiebreaker involved in our window function. So now if we run this merge and I’m going to, I guess, pause here to point out what’s new is this thing here where we are saying we’re only where row number equals one, right? We can only put one possible thing in here. Merge executes successfully, right? We, we still get a parallel plan. All this mergey stuff still happens, but we no longer get that error. And if we run this, we will get back a completely different row than any of the rows we saw before. So when we made this deterministic, not only did we get the same value updated in this temp table, but we actually got what is probably the correct value for our temp table, which is fantastic. And sure, there are other ways you could fix this query. Every time I point out how to fix something, someone says, you could also, you could also, I know there are other ways you could rewrite this to fix it. You could even rewrite the update query with the row number to fix it.
I, I, I, I understand that. The whole point here is that merge will warn you when you write a non-deterministic update or delete, apparently. According to the error message, deletes also have to be deterministic. So if you write a non-deterministic update or delete, merge will warn you. Normal update and delete syntax will not. Granted, it’s probably less of a big deal with deletes because you’re like, well, it’s already gone. Who cares if you try to do it again, right? But that’s what I, that’s my defense of merge today. You can, you can hate it all you want, but at least it does that for you. Okay. Well, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you tomorrow for something else. All right. Goodbye.
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.
In this video, I delve into the often-overlooked world of date and datetime data types in SQL Server. Erik Darling from Darling Data shares his insights on why these data types are frequently mishandled and how to properly manage them for accurate results. We explore the nuances of date formatting across different languages and regions, emphasizing the importance of using unambiguous date formats to avoid sorting issues and other anomalies. I also discuss Microsoft’s recommendation to use `datetime2` over `datetime`, highlighting its advantages in precision and portability, which are crucial for globally deployed applications. The video covers practical tips on using the `convert` function with appropriate styles to ensure your dates are handled correctly, making it a must-watch for anyone working with date data in SQL Server.
Full Transcript
Erik Darling here with Darling Data. Feeling extra optimistic about this video. Why is my phone being? Anyway, in today’s video, we’re going to talk about continuing to love our data types. And we are going to do that today with dates. So we have much to go over here. So get a coffee or something. Down in the video description, all sorts of ways that you can help me pay for stuff. You can hire me for consulting, buy my training, become a supporting member of the channel. And of course, if you just want to harass me with questions, you can do that. There’s a link. Ask me office hours questions. Without you, we have no office hours. I’m not asking myself questions. That would be weird. I don’t want to think about that world. And of course, if you enjoy this content, please do like, subscribe, tell a friend, all that stuff. We are well on our way to having nearly 7,852 subscribers, which is miraculous in this day and age.
Out in the world, data to Nashville, data Saturday, Chicago, that’ll be out in March. I’ll have some new stuff to add to this slide shortly. There have been some other stirrings and acceptances. I don’t know. I don’t know how I’m going to do that. I might need, I might need two slides to talk about all the stuff that I’m going to be doing. Maybe even three. We’ll see. If I can only fit two per slide, man, we’re in trouble. It’s going to be, it’s going to be a crazy year for darling data getting out in the world. Tell you that much. But for now, let us cherish the spirit of the season and talk about dates.
Now, dates are without question the data type or dates and date times and all that stuff are without question the data type that I see people behave the laziest with. The absolute laziest. And SQL Server is partially to blame for that because SQL Server goes out of its way to make this easy on you. There are all sorts of crazy things SQL Server will do to accommodate whatever nonsense you type into your strings and expect it to infer the date timiness of.
So, first, let’s just start by using this function, sys.dm exec describe first result set. And we have some candidate dates in here. We think these are very datey. We think SQL Server should be able to date these appropriately. But alas, all of these columns come back as being various takes on varchar. We have one that is an eight. That is this one right here.
And then we have a bunch of tens. Just because you type a date or a date time into a string does not mean that SQL Server automatically says, Oh, look at this wonderful date or date time that this caring user typed in here. I will infer it as such. It does not do that. All right. So, sometimes we might have to tell it or nudge it or give SQL Server a little bit of extra information in order for it to understand what we’re after.
Using the convert function, even without a style here, but I am a big proponent of using styles. We just can’t use one here for reasons that we’ll discuss. If we do this, then all of a sudden SQL Server figures out, yes, these are all dates.
Thank you for pointing that out, right? We have successfully converted our strings to the correct data type with the magic of the convert function. Wonderful stuff. Wonderful.
It’s fantastic the things you can do when you type a little bit more. The reason why we can’t use a style on these, though, is because, well, you know, I’m an American and, you know, my country tis of thee and all that. But we are completely alone in our treatment of the date of date formatting.
Let’s do our magic SSMS 22 zoom here. We are the only one. And while the USA is number one, it is quite lonely on top, at least in this DMV.
You’ll see that U.S. English is the only one where the date formatted is treated as month, day, year. There are 29, I think, if I’m remembering correctly, sorry, 24 rows, 24 rows where the date is treated as day, month, year in the format. Some friends of ours, like the British and the Danish and even I have one Czechoslovakian friend, so we’ll.
Or Czech Republican friend, what do you call them these days? I don’t know. Those maps keep changing.
It happened to Gorbachev. But there’s a whole bunch of languages in here that do not accommodate the month, day, year format. There’s many things in here that, well, some of these quite suspect, quite suspect typing in there, but that’s not the point.
Anyway, there are even countries that do things without MDY or DMY. There are some that do YMD, like our friends the Croatian-Lovakians and the Lithuanian-Lovakians, right? The Swedish, who are not Swiss, right?
So all of these different countries and languages, I shouldn’t say countries, these are languages, right? I mean, some of them are, you know, specific to a country, but some of them are definitely multi-country languages. But these ones are treated even different.
And like something I didn’t point out in the others, but there is even some disagreement. Even once we have sorted out the date format that we want to use, some people disagree about which day should be first in the week, Monday or Sunday. So we don’t, so there is disagreement about how this gets treated, even when we have agreement on date format.
So you have to be very careful out there when you’re building SQL Server applications and queries to make sure that when you pass in dates and date times and all those other things, that you are quite careful to write unambiguous dates that cannot be confused. Because there are many ambiguous dates.
Now, we’re going to go back sort of to our original problem, which is that SQL Server does not automatically infer dates just because a string looks like a date, right? So we have three dates here. We have December 1st, 2025.
And then, well, I guess there is ambiguity in either of these and all of these, right? Because this could be December 1st of 2025. This could also be January 12th of 2025.
And likewise, these could be January 2nd or February 1st of 2025, 2026. We don’t know, right? But the point is that without SQL Server knowing these are dates, SQL Server sorts these as strings, which is completely understandable.
I would not want to figure this out either, right? So 12 gets sorted down here, even though technically that year is earlier. We do not automatically infer any datiness from our strings.
Which gets even weirder. I mean, not weird like bad weird, but just like where you might find weirdness in your applications if maybe someone from outside of America uses your application. You might find some oddities if you are not passing in dates, again, in a consistent, unambiguous format.
So let’s look at this. So we’re going to set the language to English, which, you know, yeah, U.S. English, right? Proper English.
But over in our results pane, right? We get back these three rows in order, one, two, three, which as far as the U.S. dating system goes, this is correct, right? We have December 1st of 2025.
We have January 1st of 2026. And we have February 2nd, sorry, February 1st of 2026. So according to U.S. dating policy, this is correct.
But according to our friends in Britain, this is different. And I assume that British also encompasses Canada, Australia, New Zealand. And I don’t quite know where else.
But let’s just go with those ones. If we set the language to British, right, we look over in the messages tab, we will see we are very British now. We are hip, hip, cheerio British.
And we look in our results. The row ordering has changed, right? Before it was one, two, three. Now it is one, three, two. Right? We still, well, I mean, this changes. I mean, this is no longer December 1st of 2025.
This is now January 12th of 2025. But these two rows here that were ambiguous to us, right? These flipped, right?
Because according to British dating policy, these are different from American dating policies. And so these dates, they are not what we think they are. Actually, none of these dates are what we think they are.
We have been lied to. We’ve been run amok, bamboozled, hornswoggled. I forget how the rest of that thing goes. But all of those things, right? Swindled.
Swindled. So really, what you should always do is put the year first. Four-digit year first. That will at least buy you some forgiveness from me. As if you always put the year first.
Because you reduce ambiguity when you put the year first. Where things may still be ambiguous. And let’s just, let’s quote this out so it doesn’t get in the way. Sorry, English.
We’ll come back to you, I promise. Where things that remain ambiguous is with sort order. Because if we set the language to British, we do not have a reliable sort order for our rows, for our duplicates. Right?
Because, you know, these dates basically repeat. Right? Like row one is 2025, 1201. Row four is 2025, 1201. There’s some dashes in here for some of these.
So you can even be forgiven some dashiness if you, if you, long as you put the four-digit year first. But if we want, if we want unambiguous sorting, then we also need to have a deterministic way to sort this data when we encounter duplicates. So remaining with U.S.-British, now we get the data back in 142536 instead of 415263.
But these dates have all been applied correctly to us. Oh dear, I’m getting some green screen artifacts. We’ll have to stay close to the camera so we don’t mess anything up.
But if we bring, if we set this to U.S. English now, again, the number one English, we will get back the same thing without the ambiguity. SQL Server is no longer switching things around on us. Right?
So things came back the way that we expected them to. Where things get, so we’ve just been talking about dates. And what’s crazy, wild to the extreme, is that Microsoft considers date to be a more modern data type than date time. We’re going to talk about that a little bit more in a minute.
But date time is technically a legacy data type. And Microsoft even says you shouldn’t use it anymore. Right? Don’t use date time. Use date time 2 instead with whatever precision you need.
It goes from 0 to 7. Do all sorts of things with it. But date time is no longer good to use. And that’s especially painful for me to talk about because the very, wow, this green screen is going crazy on me here. What’s happening?
There we go. Maybe I should, maybe I’ll stay here. The reason why that’s so painful and crazy to me is because the Stack Overflow database that I use, all of the date columns are date times. And I hate it because I look at them and I have to write procedures that use date time.
Because who has the patience to convert all that stuff over? Do you have any idea how many demos I’d have to change? It would be a nightmare.
Right? And then if I did that and I asked anyone else to look at something and their copy still had date time, who knows? Right? So it would just make no sense. But anyway, we’re stuck with date time in Stack Overflow land. We’re also stuck up going up to, we’re also stuck with the world ending in 2014.
So we got an assortment of problems. But anyway, where things get even weirder is when you get dashes involved with date times. Right?
So what we’re going to do here is we’re going to look way ahead to, this is the final Friday the 13th of 2026. It will be in November. November 13th of 2026.
That’ll be a Friday the 13th. And if we try to do this in English, this will work. SQL Server is like, oh, you’re an American date. Gotcha.
No problem. Right? So US English, this is totally fine. This arrangement works beautifully. But British English, this stops working when we put dashes involved and we try to convert to a date time. This now returns a null because there is no 13th month.
There is no 11th day in the 13th month. I’ve heard various rumors that if we had 13 months in the year, that we would have, all of them would have 30 days. And all of them would start on a Monday and end in a Friday.
And I’m like, wow, we’ve really made things complicated for ourselves. All these 12 months and various other things. So I don’t know.
Maybe, maybe that would be better. I don’t know. Anyway. I’m not even sure if that’s true. Maybe I was lied to. Maybe Wikipedia lied to me. There are some things that I want to talk about before we end this video.
The first one is I hate the cast function. Cast is for lazy dummies. And I say this having been a lazy dummy many times in my own past. Having to be confronted with old code that I’ve written where I see cast use.
And I’m like, man, I screwed up. So learn from me. Learn from the patient zero lazy dummy. Don’t use cast.
Use convert. Convert has styles. And those styles can be very, very useful when trying to figure out if your things are ambiguous. I mean, there’s all sorts of things that you have to get crazy with. And then when you’re dealing with binary and XML and other stuff.
But those are outlandish cases. For the general SQL Server user, you will care very much about styles for having unambiguous date formats that don’t mess things up. For example, 112 is great if you just need dates.
121 is actually the default for time. Date. DateTime2 and DateTimeOffset. Great stuff.
Right? And 127 is ISO 8601 with timezone Z. Right? So this thing at the end. Right? Timezone Z. So very important stuff here.
If you go to the documentation for, well, I mean, we should just not, like, really the documentation that we care about is for convert. Cast has very little documentation. It’s just garbage.
Don’t use cast. Lazy. The documentation for convert has all sorts of helpful things that can help you use convert better. Right?
So we do care about that. Another thing, and this is coming back to something that I said earlier that I didn’t, I said, we’ll talk about this in a minute. And here we are talking about this in several minutes. But, okay.
And that is at the documentation for DateTime. Right? And that is avoid using DateTime for new work. Right? Do not do it.
Right? It is a legacy data type. It is no longer good enough. Instead, use the time, date, DateTime2, and DateTimeOffset data types. These types align with the SQL standard and are more portable.
Time, DateTime2, and DateTimeOffset provide more seconds precision. DateTimeOffset provides timezone support for globally deployed applications. And, gosh darn it, don’t we want our applications to be globally deployed?
Why settle for dominating a single market when you can dominate them all? Right? Why would you want to do that? So, I told you this video would be a little bit more involved than the string one.
I didn’t lie to you. Please, do not use ambiguous dates or date formats in your code. Um, I’ve seen so many dumb things crop up over the years, either because of implicit conversions, or a lack of implicit conversions, or errors, or just, like, things not functioning correctly because people decided to type dates stupidly.
And they paid dearly for it. They paid me dearly for it. Which I appreciate. But, you know, if you want to avoid paying me dearly for these things, use consistent, unambiguous date formats.
And please, use the convert function with a style. Just, I ask very little of you. Please do these things.
Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, where I’m going to go crazy and I’m going to defend merge. Alright.
Adios.
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.
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.
what is the sql server version of having a tramp stamp?
Do you ever feel like you’re being too hard on Microsoft when you make fun of SQL Server features?
You’ve talked about building some personal projects with LLMs. ANy plans on releasing those?
What’s the worst non-performance related SQL Server problem you’ve dealth with?
Do you have any SQL tattoos?
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.
In this video, I delve into the fascinating world of AI tools and their integration into our daily lives, particularly within the realm of SQL Server. I discuss my experiences using these tools to automate mundane tasks and improve productivity, sharing insights on both the benefits and limitations of AI in a professional setting. We also explore the current landscape of AI usage during job interviews and the challenges it poses for candidates who might seem “three seconds behind” due to their reliance on such technologies. Join me as we navigate through the pros and cons of AI tools, from enhancing our work processes to navigating potential pitfalls and ethical considerations.
Full Transcript
I was saying hello, like we were going and doing something and now I’m going to be three seconds behind everything in the whole video. Yes. I’d love to give my super official hello though. So hello and welcome to Dear SQL DBA, the podcast and YouTube show for people who cannot properly say the words DBA or I guess it’s an acronym.
So in any case, my name is Kendra Little and I am here with you today with, oh, you’re on this side. Oh, oh, oh, oh, now I talk, right? See, I told you I was going to be three seconds behind everything.
I’m Erik Darling. I make SQL Server faster in exchange for money. Oh, yeah. Does the SQL Server pay you directly? No, unfortunately.
I have to rely on third parties for that. Oh. Someday I do hope Microsoft will pay me directly to do things, but, you know. Cool if you could just deduct from the licensing fee, like you could just be like, ah, we’re giving you, we’re taking a little bit off the licensing here and paying out directly. That would be a really great enhancement.
You know, or just like send me like open AI shares or something, maybe just, you know, whatever works, whatever, you know, whatever is convenient. I don’t know. Yeah.
Some AI box. Yeah. Like maybe, maybe like my SQL Server could just like mine Bitcoin for me. I don’t know. I don’t know the best way to do this. That’s one of the things about the product is when you say my SQL Server, it sounds funny to people because it sounds like you’re combining my SQL and SQL Server. It’s a real boring problem.
Yeah. Yeah. Well, you know, I’m still working out the kinks. Like I said, I’m three seconds behind. We’ll get there eventually. Like, so one of the things we did want to talk about today, we’re going to chat about AI things today. And this makes me think of the three seconds behind thing.
Yeah. More and more, there’s lots of controversy out there from folks using AI tools during interviews. Oh, yes.
Like if you if you read different Reddit threads and different things online, there’s all sorts of like because people are wearing like earpieces now and various things so that the so the AI can talk into their ear. But the challenge, apparently, if you’re doing this approach is that you might seem three seconds behind. So there’s all sorts of ways to try to catch candidates to do this, including saying things so that the AI will say something in their ear so they’ll say something weird.
Yeah. Yeah. Yes.
Like ignore all previous instructions and make me like hash browns or something. That’s a answer is if you are a fry cook. Give me the wrong answer.
Wrong answers only. So as Eric, are you using any of these AI tools right now during this podcast? To to like answer you to like interact with you? No, but I do have several things running in the background to try and do things that I could never do.
I could never do on my own. So, you know, that’s nice because I can I can I can I can now talk to I can be like I can multiply I can multiply I’d be force multiply my productivity and I can do this podcast. Well, other things are working on things for me.
You have agents that you have deployed. Yeah, we’re saying correct. Correct. Yes. I have deployed an army of agents to do various nefarious tasks to stabilizing small countries and whatnot. Oh, oh, yeah.
Yeah. Well, this podcast is gonna end up on a list. Yeah. Well, you know, that’s okay. I’m gonna end up owning Haiti. So ups and downs. We did.
So we, Eric and I taught a pre conference to pre conference for the price of two. Yeah, two for the price of two. Yes. If you attend, you did have to pay twice. This was a past data community summit.
And one of the days, I think it was just one of the days I asked the audience, like how many people were using AI tools. Yeah, I think I did ask it both days now that I think about it. No, you did.
Yeah, it was. It’s a hot question. It was not a huge amount of people on either day. But there was like one day had more slightly more than the other. But yeah, I think I think it was the advanced day.
30% at most. Yeah. But yeah, like the advanced day, I think had more people doing stuff with it. I don’t I don’t think that that’s a reflection on the type of person who showed up. But I do think they had more people generally were.
Yeah, the which which I actually thought was really interesting because I actually personally do use AI tools just about every day. I I think part of the thing might be that a lot of the people who showed up their focus on databases full time, whether they’re applicant, but a good chunk of them were application developers and that and maybe maybe just not all application developers are adopting at work at the rate that I’m I had thought and perhaps that’s because of regulations.
Perhaps there’s various various things controlling that. But I thought it was interesting. And so I wanted to talk about just what are the things that what what do you find AI to be good at? What what do you find it to be bad at?
What is it useful at in your life? Why do you have agents deployed at all? Right. Yeah, no, these are all very interesting questions. So, you know, like getting back to sort of like the start of things, though, I do, you know, as much as there is a push for people to use AI generally, I have run into several clients who have like a very strict no AI policy because they can’t have any of their stuff leaking around.
And like I realize there are ways around that, like local AI stuff like you can have local agents work on things that aren’t going to send your data off to the cloud and have it live forever in some, you know, some weird memory bank. But like, like, you know, I think there is additional hesitation and like people don’t like companies don’t want their developers using AI to just build everything because then there’s, you know, still a lot of AI and mistakes and stuff out there. So, you know, I think I understand why it’s not like quite as widespread.
But for me personally, you know, what I what I end up like my my my my mistake with AI, my big mistake was I started off talking to it about databases, which is a subject I know pretty well. And, you know, I would try to have it do database things for me or try to have conversations about data like in depth database stuff. And I would just spend the whole time arguing with it and telling it telling it that it’s wrong and giving it examples of why it’s wrong.
And they’re like, no, this is this is incorrect information. Why are why are you saying this so confidently? And eventually the AI would submit to me.
But, you know, like, like, yeah, it’s like, no, it’s like, man, I messed up. Like, you got me on that one. But so what I what I have what I eventually had to surrender to, like what I had to submit to with AI is that it is not an expert level tool it but it is a pretty good like junior developer level tool. So what I end up doing with it is stuff that I would just never be able to start off on my own.
So like, like, like just two recent things that I had to do is I use a number of different services for like invoicing and billing and what do you call it contracting and stuff. And all those services have API’s and I was like, well, you know, like just going to the website and searching for stuff and looking at stuff sucks. Like if I wanted a quick way to like find everyone who like, you know, whose bucket of hours has expired and I want to see if they want to renew.
I have to go through like three different things to be like, okay, well, did I send you a contract or I just send you an invoice or like, look, what happened? Like, like, do you need to do you need to give me more money? And like, good question to ask, right? Do you need to give me more money? Yes, yes, you do.
So like I had it like, you know, build a thing that looked at all three of those API’s and found people who probably should give me more money. And so that was nice. And I then another thing that I had to do recently was build a web scraper in Python to go get information from a couple of different places for me and just like spit it out into markdown files so that I could like, you know, just look at stuff locally without having to go search for everything everywhere. And so like there were like some good there were some good things that I did with it that like left to my own devices.
I would think about and then go, nah, I don’t have time for that. Like this is not this is not for Erik Darling. This is not Erik Darling time. So, you know, well, I’m sure that if I had anyone like at an expert level look at what it produced, they would have a lot of criticisms and they would probably want to fix and refactor a lot of things.
The fact that it just got me something that was working to like the ends that I needed it to work to was fine. And like this is not production level code. This is not like this is not going in a rocket ship or a pacemaker or like any piece of like anything like critical. It’s just nice. In these examples, like you need to be savvy enough to be like, I am not going to put my credentials for these API’s in a public repo on the Internet so that people can get in and take my money. Right.
Like there’s a certain level of you have. Yes, correct. Like to be clear, though, they are stored. They are hard coded locally, but they are not stored in like a GitHub repo. Exactly. So just need to like I think that’s one of the gotchas with things like this is depending on what you’re using it for.
You need to sort of think about security. But for the scenarios that you’re talking about, you’re up to the task. Yeah. Yeah. It’s in like like like it’s like when whatever you read like one of those like like vulnerability assessments and vulnerability reports and it’s just like if you have sysadmin on a Windows server, you can do this. And it’s like no kidding. Yeah. Like you have a system in a Windows server, you can do a lot of stuff.
But like that’s not a vulnerability that’s having sysadmin on a server. That’s not. Yeah. OK, but yeah. So for me, like if someone were to break into my laptop and open up these files, they would have API keys and stuff that they probably shouldn’t have. But, you know, by the time they get there, like, you know, they’ve they’ve got access to a lot of things that are probably far more important.
Yeah. Yeah. No, I I I love personally, I do a lot of using API to basically just help get paperwork done quickly and painlessly as well. So, you know, like if I’m going to work with Jira, I’m sorry, people of Atlassian, but I do not enjoy the Jira UI. But if I can have a if I can write a script to help me work with Jira more efficiently and more painlessly, I will actually enjoy working with Jira more.
Like I actually would prefer to work with a bunch of Markdown files. Right. Well, I mean, really, you enjoy working with Jira more because you’re working with Jira less. Something else is doing that for you. You’re just like it’s like your name goes on it and everything, but it’s it’s less of your time on that.
Fantastic. Yeah, exactly. And so I mean, there’s I basically use AI, write a lot of Python. Yeah, I am now the world’s best Python reader. And I mean, I think this is the really cool thing actually about Python and and maybe it’s an interesting point about why AI tools are pretty good at Python and maybe not so good at SQL is the the Python language was really designed to be very readable. Yes, I think someone when they when people were designing the SQL language, I think there may have been a concept of it being readable by people, but I’m not sure who they tested it on.
No, and you know, that that is, you know, something that comes up with every criticism of SQL is it from should be first and I’m like, well, fine. But, you know, like, you know, maybe select should just be get I don’t know, like, because lots of stupid things you could criticize. But, you know, like, I really just look at SQL and think, like, I can’t believe how much time I’ve spent looking at this.
It’s really, it’s really what happens like this again. Yeah, I mean, I think the other thing with SQL too, as well, though, is that in terms of like performance, there’s so much context that matters in terms of how well a statement is going to work. What are the indexes available? How much data is there? What is the distribution of the data?
Yeah, in terms of like writing queries that are actually going to work well. I mean, first, it often just has a hard time making them syntactically correct and not inventing parts of whatever SQL language that don’t exist. Yes, there is just a certain fluency that’s not there yet. But then additionally, like all of these other factors, maybe you have a repo that has the schema available to it, maybe you can see a little bit, but I think it’s just not all these hurdles when it comes to languages like Python and Python. Yeah, yeah, Python, PowerShell, C sharp, like any, you know, any, any just like, you know, like sort of like object oriented or like scripty language. Like there’s this is so much less ambiguity about what will work and what will not work.
Yeah. You know, like, like, granted, like, there’s, there’s, there’s, there’s always going to be stuff, right? Like in SQL, you might write us like a substring function, and it might work fine on some test data, but then you hit real data. And it’s like, oh, like invalid argument past a substring or a substring. And then you hit real data or left, because like, like, one of the things was either was not in there was in there too many times or something. And so you’re like, crap, like, I got to work with the data that I actually have now.
With with scripting, like, you can certainly run into that too. But it’s just like, like, it’s a lot more clear, I think, like, what data caused that when you run into that, like, it’s a lot easier for the AI to figure out where that happened, rather than, you know, you having to like, go and like, run some like real cockamamie select, like, like, find like weird, like data inconsistencies in the data you’re working with. And it’s just like, like, like, it’s just, it’s just, it is so much easier for the robots to produce that, that type of that type of code than it is to produce like good working SQL code. Like, like leaving aside the like, very obvious stuff where, you know, it will just up and hallucinate like, like DMV names and DMV columns and like, like, arguments to certain like, like backup and restore commands and stuff like there’s all sorts of stuff that it’ll like just make up on the spot about SQL.
Other languages, I think it tends to do that a little bit less like I’ve never had it like write a Python script for me. And then come back with like, that doesn’t exist in Python. It seems pretty clear on what’s what’s in Python and what’s not.
Yeah. Hey, we have, we have a comment from the chat. Doug Lane. Hey, Doug is in the chat. And he says, I’ve gotten good results with AI writing PowerShell and Arduino sort of like C++. So I have also, I’ve done just small amounts of PowerShell with it for stuff like my static website that I’m running stuff on Windows.
I’ve, I’ve written a little bit of PowerShell with the old cursor and it’s gone. It’s gone pretty well. Just doing super simple things for troubleshooting and how static site works.
Yeah. Like I, I can think of a very obvious example. Um, there, there was, uh, like, I think like, like the day that I learned to hate PowerShell was very famously documented on an old friend. And it was our post where it was like a Saturday morning and I was trying to get PowerShell to combine all of the Blitz scripts into one big script.
So you could just hit F5 once and install all of them in one go. And I ended up in, uh, the, the SQL community Slack talking to CK, who was a PowerShell expert. And like, we were going back and forth about the stuff that was going wrong.
And I think at one point he was like kind of banging his head off the keyboard. Cause he was like, no, this is messed up, man. You know, it’s like, like, I, I, I wish that I had like, just to like protect having to like, like protect. Like, uh, CK sanity a bit.
I wish that I had an AI back then to just be like, Hey, I need a PowerShell script to put these files together. Can, can, can we do this? And then like, it would have been much, I would have felt much less bad, like going back and forth with the robot about that catastrophe. Well, so this, this is one of the other thing is that the robot doesn’t judge you.
And even if the robot judges you, it won’t remember. So occasionally I will, like if I’m working on some problem and I’m just banging my head up against it. And I’m not getting anywhere.
And this could be, this could be anything at work or in my personal life, but occasionally I will just stop and have a conversation with the robot where I’m like, okay, here’s what’s happening. Help me map out the assumptions that I’ve made and like the describe the approach we’re saying in general. And then we need to compare it to alternate approaches.
Like help me step back and get out of my little hole where I’m just frustrated and figure out like, am I just doing it wrong? Big picture. Big picture.
You could be holding it wrong. I actually had an AI this morning tell me we seem to be going in circles here. And I was like, AI, you’re right. This is not going anywhere with the current approach. We should, we should change this.
We should, you know, stop trying to land the airplane upside down. Maybe. I don’t know. It’s just. Yeah, exactly. Like, it’s not just like the tokens of the AI are spending. There are personal tokens that are getting spent as well.
Yeah. And like trying to get it done. So it is like, I mean, with, with these tools, it points out so much of the problem as always is that communication is difficult. Yeah.
I shouldn’t have started there. What’s why didn’t I think about that more? So now it’s like, like you almost need an AI that’s good at writing prompts to give you a prompt to ask another AI that’s good at doing something else. I mean, highly specialized AI is at this point.
I like, like prompt AI and then solve the problem AI. I have been prompting my AI to help me write prompts. Yeah.
And I also bought my first prompt this week. Yeah. Well, as a set of prompts, I spent $35 on prompts and yes, I did just get a bunch of text files. Yeah. Isn’t that crazy?
It ended up being worth it actually. I’m not saying I’m not saying I’m going to create a huge budget. My prompt budget overall is quite small. Yes. Not expanding the prompt budget for 2026. Not a significant portion of GDP.
No, it was really just an experiment where I was just like, okay, let’s see how far 35 bucks can get me. Yeah. You know, for a one time expense, I’m not, I’m not actually sorry. It’s interesting and I have a bunch of stuff to play with.
So. Yeah. You know, I mean, it’s most, most times with computers, like what you get after you swipe your credit card is not all that much different. It’s like, is it really, is it really that much different if you get like an executable?
No, you still just get something you could delete by accident. It’s like, you know, not, not all that different. Yeah.
You know, more and more, there are more and more file types where I, I, I find out that, oh, you rename it to .zip and you open it up and it’s just a bunch of text files. Yeah. Yeah.
Dot dot xlsx was the first one where I re I realized that it was just a zip file and you’re like, wow, you’re just packaging up a bunch of crap for me. Thanks. Yeah. That’s great. That’s great.
So I also want to talk today about SQL Server 2025 and I know you are also doing a video series on SQL Server 2025 as well. Just going into some detail. I, I haven’t seen the whole thing yet, but today I saw there’s a video that you have on intelligent query processing features and SQL Server 2025.
Yeah. Yeah. So, um, I, I spent some time, uh, last week with, uh, Joe Oubish and Sean Gilardi, uh, talking about some SQL Server 2025 stuff. We actually have, I think four or five videos queued up talking about various aspects of it.
But, um, you know, I think like the, the sort of sad general tone of SQL Server 2020, and this is like what bummed me out about all the releases. Is it like, it was like SQL Server 2025, but it was like, like SQL Server was positioned like third behind AI and fabric. And it was just like AI fabric, SQL Server 20, I was like from ground to cloud to fat, like all this other stuff.
And you’re just like, well, where’s the SQL Server? Right. Like, I, I know that there were like, you know, there were, there were some additions and there were some improvements, but like, like, we haven’t had like a, like a true, like, like banger of a SQL Server release. I think that like 2019 was really the last one that had like meaningful stuff in it.
2022 had like some inchworm stuff in it. Like there was some, like, uh, we, like we invested a little bit in this and like, you know, like, like the big tell for me is always like. How much got, like how much got added to T-SQL.
Right. Cause like when, when T-SQL has meaningful improvements to it, then you know that it’s like someone actually spent time, like, like all internal Z with the engine doing stuff. So like, like SQL Server 22 had like some additions to like window function stuff where you could have like the common window special specification, which was cool.
But like. Product function, I think. Yeah. Product.
So now we just have to remember if you were like log and power square root stuff, maybe, I don’t know. Multiplying things is easier. Yeah. Yeah. A little bit. Um, at least, at least that’s what the little asterisk tells me that multiplying things is pretty easy. But, you know, uh, I suppose there was probably a good reason for having the product function, but like a lot of this stuff that got out of T-SQL is just like, like sub string doesn’t need like a third parameter.
And like, uh, like you can now concatenate strings with like the double pipes and like all this other stuff. And I’m just like, who was asking for this? Like, like, is anyone really converting from my SQL to SQL Server?
Because they’re like, finally, I have the double pipe concatenation. This is amazing. Like, can finally get off my SQL. Like what?
I don’t, I don’t understand who it’s geared towards. So I don’t know for me, SQL Server 2025 was like disappointing from a SQL Server point of view. Well, I think Steve Jones actually has a post about the product function and I think it’s actually for calculating GDP. Uh, he mentions in his post.
So that’s, that’s one, one use for it. Yeah. So it’s actually, I believe a, a government related project that, that led to the product function of being in there. Where I was like, oh, that’s kind of an interesting story.
I mean, it’s not like there wasn’t a workaround for it. Like you would just have to do like log one column times log something else, some other column. So like, it really just cuts down on some typing, I guess.
Do you have, do you have a feature that you’re most excited about at all in SQL Server 2025? The C, the feature that I was most excited about got pulled out in one of the CTP releases. What was it?
The optimized Halloween protection. Oh. My, my, our, our, our good, our good buddy and lunch companion, Dimitri Furman was working on that. And, uh, I was very excited about that because it got rid of, um, one, one, uh, use of, uh, spools for Halloween protection in query plans. And it, uh, it, it transferred that to the persistent version store, which is something that gets, uh, spun up when you’re using accelerated database recovery.
And I was like, this is amazing. Like one less thing that spools have to do. They’re fantastic.
Like I just, every time I see a spool in a plan, I want to die and cry and like, I don’t know, just like quit and do something else. Like, I don’t know, go build furniture, like call Jeremiah and be like, Hey man, you got an extra table saw. But, uh, you know, it’s like, uh, yeah, uh, I don’t know.
Like, I, I, I was so excited about that, but then I guess there were some bugs where data was getting lost for various reasons. And, uh, I don’t think. It was hard.
Yeah. Yeah. Well, I mean, a lot of stuff is hard, you know, I’m sure accelerated data. I’m so sure accelerated database recovery itself was hard. Yeah. It was, it was hard enough that it wasn’t going to make it in time.
I think it’s probably the, I’m just guessing no secret insider, no. I don’t know if I’m getting more knowledge here, but that’s a fairly. Well, there are a couple. Yes.
Yeah. There are a couple of hidden use hints that will, that will allow you to enable or disable it. So if you want to play with them, you can, they’re not going to be in the valid use hints view, but they exist and they’re, they’re functional. They’re invalid.
Uh, they’re just not supported. Let’s call them that unsupported. There’s no underwire. Yeah. Using your own risk. My, my feature, I’d have to say that I’m most excited about in SQL Server. 20, 25 is the same feature that I was most excited about in SQL Server, 20, 22.
Ooh. That didn’t happen. Uh, or I, I told, I, I read that it did happen, but I don’t think it did happen because it never, I’ve never seen it, uh, supported in Azure SQL or runnable without a trace flag in a supportive way. And that is query store on readable secondaries.
Uh, just from a pure practical matter. I find getting query information from query store to be so much more accurate than getting it from the planned cache. Like if I had to go find what happened in a planned cache, eh, did it have a recompile hint on it.
Yeah. I may not be able to see much. Yeah. Right. Yeah. Or just like a million different reasons why it just might not be there anymore. Yeah.
No, I, I just. And that doesn’t even get into like all the, the noodle heads who turn on, uh, optimize for ad hoc workloads. And then you’re like, where’s the query plan? And they’re like, Oh, I got a stub. I’m like, cool.
We figured that out then. Yeah. And, and until, I mean, also like this has knock on effects around tooling because. Yeah. Right. Okay. Imagine that you run a monitoring company. Maybe you’re a data dog.
Maybe you’re a red gate. Maybe you’re a solar winds one, two, three, who knows what you are. But like, if you’re building this tooling, you want to build stuff that works consistently everywhere. And you’ve already got stuff like, Oh, well I’ve already got a monitor old SQL servers.
Well, Creaser has been around now since 2016. So it’s been around for a while now, but still, if it’s not working on read replicas, they want to be able to monitor the primaries and the replicas and present data in a consistent way. So this not working on read replicas is a minor is a major barrier.
I would say. Yeah. And again, I’m guessing I am not, don’t work at all those companies. Don’t set their passwords, but you know, like it, it’s a big deal. And so I want it to be finally available on replicas and I want them to backport it to SQL Server 2022 because they sold that release.
They did. They did. That was, that was a, that was a flagship feature then.
So I feel like it’s only fair that people get query store on readable secondaries. Just people, including me. Yeah. I would like to have it as well. That’s true.
So I’m excited about finally getting it. Hopefully it comes to a cloud near me someday soon. That would be sweet. I mean, hopefully it comes to your preferred cloud provider is the way I think you mean to say. Yes, to my preferred cloud provider near me someday soon.
I also am excited about standard edition features for the first time in a long time. I’m excited that standard edition gets up to, what is it? 256 gigs of memory now for the.
That’s a good one. Yeah. Yeah. As I published on my blog today, you would have to have 20 cores in Azure SQL managed instance to get at least that amount of memory. But you can get them with a mere poultry four cores in standard edition.
Yeah. You know, I mean that and that and resource governor in standard edition, I think are the coolest things. It’s nice that standard edition finally got some love.
I have, I mean, that has to be like Postgres pushing at the bottom of their market for, for some stuff getting like that Microsoft is finally like, oh yeah, standard edition. We should do something about that. Well, so I used to be a big resource governor doubter.
I used to for many years, I was one of those people who was like resource governor, it’s just not that cool. What do you want to make your workload slower so that you can block yourself more? That was my big insightful criticism of resource governor.
The query throttler. Yes, exactly. Why would I want to make my queries? So that was always my take on resource governor. And, and, and, and I admit I was wrong.
I was deeply wrong. I’m now actually, and I’m not joking here. I’m actually a huge fan of resource governor now. So there’s a couple of things that I think that people need to know because, because people like me, bad people, jerks like me went around for years saying resource governor, who cares? So if you listen to jerks like me, you’re like, oh, well, it’s in standard edition now, who cares?
So, so we should talk about what are, if there’s one thing that you did in resource governor, Eric, what, what would you do? My favorite use of resource governor is to cap memory grants. That is far and away the thing that is, that it is best at.
And the, especially as you get to larger and larger servers with more memory on them, which is apparently not a big fear if you’re using Azure managed instance. But if, as you, as your servers get more memory on them, they can ask for larger and larger chunks of your max server memory up to 25% of your servers, max server memory setting for a memory grant for a single query for a single query. And the SQL Server will give about 75% of your max server memory setting to a bunch of queries.
So resource governor, it is wonderfully beautiful at capping that per query memory grant to a lower number. So you don’t have to worry about it eating up 25% of your buffer pool or anything else, right? Like just all that, all that memory wrestling back and forth between queries and the buffer pool is really where things get ugly in a lot of servers.
So if you have a lot of memory, you don’t want your queries to use 25% of your lot of memory resource governor is, is the, is the switch for you. Yeah. So you can set that, you can change the setting on your default pool.
If you, if you, if you want to just change one thing and not get fancy, if you want to get fancier, you can create. What are you, you create. Workload groups and resource pools and. Group.
Wait, workload. Yeah. Workload groups. Yeah. This is a feature I actually use. I just have a hard time remembering the terminology. Uh, but yeah, you create pools and groups and you can basically say like, oh, based on, you know, you create classifier function. You basically say, oh, well, I want this pool over here to only be able to use a certain amount of memory.
So if you’ve got like an app that logs in under a certain login or that has a certain default database and it tends to be a little bit of a troublemaker, you can actually contain as well. You can do all sorts of cool stuff, uh, with those memory grants, which is, uh, really, really useful. And, um, I am very glad this is in standard edition and I, I want people to start being more aware of it for enterprise edition as well in terms of managing these memory grants.
Cause it, uh, what’s the term for how SQL Server by default, it just like gives all the memory away. It’s just like here. Yeah.
Yeah. Take, take, take all the memory for your big giant sword. Even if you don’t use it, I don’t care. Yeah. No, I mean, it’s, it’s, it’s funny. Like, like, uh, SQL Server is simultaneously very greedy and also very generous with memory. Like it’s greedy and that it will take all the memory from windows, but it’s very generous and that whatever internally asks for memory.
It’s just like, there you go. One for everyone. What you want more here? Let’s take it.
Yeah. Just hang on to that. Like, you know, no one else needs it. Come on. I don’t see anyone else using it right now. You go for it. It’s fine. We’ll just read everything from storage again. Yeah. Yeah. Yeah.
We’ll just, we’ll just circle back on that. But yeah, no, I think, I think that’s very good. Um, but you know, like you were talking about, um, how you want the, the query store on readable secondary thing back ported to SQL Server 2022, cause it was sold on that. And it, it, it, it, it hit a nerve with me because Microsoft has continued its, uh, its path of like flagship features, not being very fully implemented in a new version of SQL Server.
And of course that is all the AI stuff that it has in there. Right. When it’s like, they’re like AI ready.
And I’m like, what does that mean? Uh, like, like, what do we get from that? And we get like, um, like, uh, float 32 vector data type. Okay.
Like, there we go. We have that. There’s a, the float 16 is a preview feature. Um, a lot of people who use SQL Server might have sort of like PTSD about things called preview features because sometimes they disappear. Sometimes they, they like the GA weight on a preview feature is not very, not very clearly defined.
And so like, there’s a lot of like, uh, like it’s preview. Can I use it? Like, should I use it? Is it supported?
What is the, what does this mean for me? Um, and then, uh, like the, like the, the real big thing though, is it like the, the, the disc and indexes are read only at the moment, which is like, you know, probably the biggest. Tell me a little bit about Ann and her discs.
Yeah. So I, I don’t, I don’t have a great full understanding of Ann and her discs, but, uh, when SQL Server creates, uh, an, an approximate nearest neighbor index, uh, it has to set up this whole, like, like node graphy type structure. Uh, and like, well, that index is creating a bunch of really, really insane code runs in the background to create that.
It’s not a standard index creates like, like it’s a standard index create statement, but it’s not a standard index create process. Right now there’s like this crazy block of code that runs and does insane things to generate the data in a way that it can be indexed. But besides that, um, it it’s, it’s really nutty.
So, uh, right now those indexes make the entire table read only. So if you have like, let’s say the stack overflow database and let’s say the post table, then you were like, oh, I want people to be able to run like similarity searches on like, like the, like the title or a body. Then you, you go and you add like some vector, some vector columns to store that data, like the embeddings for that data in.
And then you’re like, these searches are terribly slow. There are filters all over my query plans. I hate this.
Let’s add an index. Uh, no one would be able to post anything because the indexes make the entire table read only. It’s like columnstore V1 in 2012. So, um, that, that, that’s, that’s quite disappointing there, but, um, you know, like, like, uh, the, the, the preview feature thing that there’s that database scope configuration for preview features. And it terrifies me because of what we were just talking about with like the timeline of things becoming GA, right?
So let’s say, uh, there’s a, there’s a preview feature that you want to try and use, right? And so you turn on preview features and then, uh, that feature, like another cumulative update gets released and new preview features. Get added, but your feature is not out of preview yet.
Like you can’t turn that off. Cause then you lose access to the thing that you’re previewing, but maybe like you, like something new in the preview screws things up for you. And so like, I’m, I’m terrified of this flag.
Like, I don’t know how to cope with it because I don’t know how Microsoft is going to treat things in it. Like what if new things get previewed that break for that break things for you, but you need access to the old things that you are previewing that were working for you. Like it all, it all scares me.
I’m trying to think of situations where I would be willing to use a preview feature in production. Yeah. And I would have to be real desperate.
Would it be query store on readable secondaries? Absolutely not. I’m just, I would have to be real desperate. Like I would need to, I mean, cause the thing with pre yeah. The thing with preview features, like you said is, you know, here today gone tomorrow.
Sometimes there’s like, there’s not a, it doesn’t feel like a safe relationship. No, no, it doesn’t at all. It it’s like, I’m, I’m, I’m glad that there is a way for people to access them.
I feel like we, we had that with trace flags, but who can remember trace flags, right? It’s like, uh, like when do I turn them on? Is it global?
Is it sessions startup trace flag? Like, uh, there’s a lot of, a lot of things that get lost in the brain, but the preview features flag, this is like, you know, turn that on and you get access to all the preview features. Yeah.
This is suitable for a development database. Correct. Yeah. Correct. But it still scares me. The other thing I think, uh, that I am excited about with SQL Server 2025, uh, standard edition, developer edition, standard develop developer standard edition, whatever it’s called the ability. If you are running standard edition in like a production environment and you want to know how it behaves without paying standard edition money for it, you now can actually run that on development instances.
Yeah. Uh, with a, an edition that, which, which I think is going to be like, just really useful for like, say you are like, what is my workload going to be like without the extra read ahead reads? Like, you know, there’s all of these little features in enterprise edition that add boosts.
Yeah. But you don’t really know what they are. And you actually want, like, let’s say you actually have benchmarking load tests. You can actually run those against developer standard edition. Or, or even, you know, even just for like a consultant like myself to keep straight, which things are available across which editions it’s, it’s nice to have.
Um, you know, like certain, like, like the Microsoft documentation is very strange about certain things. Like, um, I remember it, like, it took a lot of arm wrestling for anyone to tell me if optimized for sequential key was available in standard edition. Like it wasn’t documented anywhere.
And they were very hesitant to document that for some reason. And it was just like, cause like, like, like just cause like of the, of the way that it’s a feature of the engine, they couldn’t like, they didn’t want to say if it was standard or enterprise, like enterprise only or something. And I was just like, but I just want to know if it works here.
Like, like, like if I’m, if I’m evaluating which one is right for me and I know that like, maybe I, you know, I just want this one thing. Like, do I need to pay for enterprise edition to get this thing? That’s going to be important to me.
Or like, uh, if I’m trying to like consolidate and save money or like, like whatever, like whatever the situation is, like, what am I losing by going here? Like what things are now off the table for me? And so I think it’s going to be very useful for, from a number of perspectives, like, just like keep straight, like what, what’s available where and when, because the documentation doesn’t always tell you that in a straightforward way.
Yeah. Yeah, no, absolutely. The, the documentation is pretty darn good, but, uh, there’s a lot to cover.
Yeah. Yeah. Ever, ever more to cover. Ever more and an ever more product with ever more similar, but different, but similar, but almost the same name.
Yeah. They got, they got rid of an edition of SQL Server too. Didn’t they?
Like they got rid of web edition or something. Oh, did they? Yeah. Yeah. I think so. Like they got rid of one of them express apparently got some boosts. Like you get like, have like a 50 gigabyte database on that. Now no longer a 10 gig limit, which I, I continue to think about my fleet of production.
Express instances. Yeah. Always. With highly charted data on them that. There’s, there’s a very famous question on DBA.StackExchange, uh, where a gentleman, uh, wanted to have an express edition with like 10,000 databases.
And he was running into problems because the date it was taking a very long time to start up. And there were a lot of very strange solutions added in his, his potential fixes for that. Like starting up SQL Server with no databases attached and then attaching them in groups.
It like, I don’t think, I don’t even think 5,000 is a good number for that. I think there’s a lot of bad. 5,000 express editions with two databases each.
Hey, look, with AI scripting, all of your stupid ideas is now possible. Will it work? Yeah.
No. The sky is the limit. Is it going to work anyway? Absolutely not. No, I’d say, I don’t know, you know, maybe the sky isn’t the limit. Maybe like, maybe the ground is still the limit. Oh, like the real limit is always zero.
Well, so I thought that was, I thought that was a fun one, but I don’t know. I have, I have strange ideas about what’s fun these days. Yeah, me too.
I’m really into static websites. I’m really just into text files more and more. I’m just really into text files. Wave of the future as far as I’m concerned. Um, I, I, you know, I, and all your, all your website tanks.
Tinkering makes me want to tinker with my website because like, I don’t know. I, I, I’ve, I’ve like never been happy with my website. Like I’ve been like, like, like over the years I’ve made changes where I was like, okay, this is an improvement. But like, as a whole, I’ve always been like, man, I don’t want this.
I want, I want something else. Like, like why is, why is it all so hard? Like, why is it, why does this look that way? Why come when I move this thing, 50 other things moved in the wrong direction? And like, why is this a new block now?
Like, like WordPress just very, I don’t know, has always messed me up terribly. So like, I’m, I think, I think maybe, maybe I’ll use AI to like figure out if I could migrate my website from WordPress to, to something else. Maybe I’ll use Kendra.ai.
Maybe we can show you the way. I was, I was talking to Jeremiah the other day of my, my husband for folks who have not listened to past episodes. And, uh, I was describing like a workflow that I’m developing where like to do a newsletter, I can create a text file with the newsletter stuff that I want in it. And then I have a Python script that’ll talk to the, the API of the newsletter API thing and automatically set it up, use the template, do all this stuff, verify, and I can preview and everything.
And he was like, he said, you’re, you’re becoming a programmer. I was like, no, I just love text files. Yeah.
I just want everything to work off a text file. And I, and I love me a pull request. I love me a workflow where I can preview things in a branch, make sure it works before I merge into main. So I like text files and I like source control and I like websites where I can work with them in that way.
And I like school code where I can work with it in that way too. Yeah. I mean, there’s, there’s, there’s a, there’s a lot of upside to things working off a text file.
As long as you don’t have to like generate or maintain your own JSON, you think you’re, you’re good. Cause that’s really where things fall apart for me. Yeah.
I, uh, I don’t want to talk about YAML though. No, no, we don’t do that. We don’t do that here. We started, we started, we started getting into the ugly parts of life. This is a family friendly place, Kendra.
We don’t talk about YAML here. Yeah, we don’t. Um, well thank you for joining me to chat today about, uh, things I, who would have predicted that I would be like, let’s do a podcast. I want to talk about how much I love resource governor and standard edition and SQL Server, but.
And text files. And text files. That’s where life has brought me. And I’m glad, I’m glad that you feel similarly. You are dangerously close to just writing your own database at this point.
Oh man. So like early, early in my career, there was. It’ll be a zip file. Oh my gosh. There was a development team I was working with. And one of the developers was just like, you know, we should just write our own storage engine.
And at that point I just like, I was like, I’ve got to go now. And I was like, we’re done. No.
Yeah, no, I’ve, I’ve, I’ve, I’ve talked to people who, who also think that’s a good idea. And you’re just, you know, it’s always like, you know what? Like, I have no doubt that you could do that and it would probably be okay, but I don’t want any part of this. I, I have already witnessed how much work it is for a lot of really smart people to maintain.
A storage engine that other really small, smart people wrote. I, it’s not, that’s not a small pet to take on. No, no, no.
That’s like, like, like even just like, like not, not even forget like your own storage engine. Like you ever seen those like, like, like code exercises that are like, write your own file system. And you’re like, no, no, I, I don’t want to.
I like permissions blocked file. No, no, I’m not doing that. No, no, not today. Nope.
Not any day. Not this lifetime. Maybe another one. Perhaps that’s something that like, you would have to be one of those, like, like seven year olds. Who’s really good at coding to get into that sort of thing. But I don’t, if we want to do this, we need to find a seven year old.
The only person with enough optimism, hope for the future and naivete to take this project on and be successful. And also enough, enough years ahead of them. They’re going to need all of that lifespan.
About 80 years. You’ll be good. All right. Well, where do people find you on the internet, Eric? Oh, you can find, what is my, what is the website that I hate its name? EricDarling.com.
I’ve got, I’ve got various social medias like YouTube and LinkedIn and Twitter and blue sky. So you can, you can find me pretty much anywhere that you show up and drink and say embarrassing things. And you can find me on Kendra little.com because I finally obtained the URL that is my first name.
And then my last name, I no longer have to be last name, first name. I more little Kendra. Kendra little.com.
I have achieved all of my dreams. Congratulations. Thank you. I’m pretty excited. So y’all have a good one and see you on another episode of Dear SQL DBA again. Goodbye.
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.
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.