Query Archives | Page 27 of 141

T-SQL Tuesday 185 Wrap Up: Video Star Edition #tsql2sday

Posted on April 15, 2025January 22, 2026 by Erik Darling

T-SQL Tuesday 185 Wrap Up: Video Star Edition #tsql2sday

Video Summary

In this video, I recap the engaging T-SQL Tuesday 185 event where community members were encouraged to share their thoughts through videos rather than blog posts. The response was quite diverse and entertaining—ranging from humorous technical mishaps to insightful demonstrations and heartfelt reflections. Highlights included Rob’s creative green-screen trick in SQL Server Management Studio, Andy Levy’s exploration of the Object Explorer Details feature, and Andy Yoon’s practical stored procedure for expanding view references. Other submissions covered topics like flat file wizard capabilities, content consumption preferences, empathy as a technical skill, and personal experiences with presenting and blogging. It was fascinating to see how these videos provided not just information but also a glimpse into the personalities behind each contribution.

Full Transcript

Erik Darling here with Darling Data, and this is going to be my T-SQL Tuesday 185 wrap-up video in which I asked the nice folks out there in the SQL Server community to record it, rather than write a blog post about a specific topic, to just record a video about anything. And I got, I don’t know, seven or so pingbacks on that. If anyone out there recorded something and didn’t ping me back, sorry. If you don’t tell me, I don’t know. So, anyway, first, you know, in true he whom the gods would destroy they first make mad fashion, the video that I recorded to invite people, I had a weird little audio glitch going on. And I thought that I fixed it, and then I didn’t fix it, and then I didn’t get a chance to re-record it, and then, I don’t know, I was fully expecting at least one of the video submissions. to make fun of my bad robot voice in the video, but everyone was just, it was kind enough to not make fun of my slight technical difficulty. I’ve managed to avoid a lot of those in recent videos, so, I don’t know, I guess, I don’t know, maybe I’ve earned it. Anyway, our first video came in, of course, I’m going to say this video came in first, but only because Rob is cheating with time zone magic.

Usually he’s cheating with normal magic tricks, but this is just time zone magic. And Rob’s video, he talks about the cool mappy scroll bar on the side of SQL Server Management Studio. Of course, that’s available back, I remember when that first came out, but, you know, at first I didn’t like it because it was, like, too big on the side, and, like, when you hover, like, this, I know it’s an option, but, like, you hover over it, and you get, like, this, like, giant, like, preview of the text in there. And, like, it just, like, got in the way a lot with stuff, you know, it’s, like, there’s a certain amount of tooling where it’s just, like, sometimes these, like, pop-up things are helpful, and then sometimes it’s just, like, obtrusive. But with SSMS 21, I think I’ve been liking it a little bit more. I don’t know why. Maybe it’s the dark mode, who knows.

But Rob actually did a very cool trick where he green-screened himself into SQL Server Management Studio. I might steal that from you someday, Rob. I don’t know when or why or how, but I’ll figure it out and do that. Anyway, thank you, Rob, for this lovely video. Next up, we had Sir Andy Levy talking about one of actually, one of my favorite things in SSMS that, this is, like, one of those things that, like, actually kind of wows clients when I’m on the phone with them, is the Object Explorer Details. So, like, you can either, like, right-click and go to Object Explorer Details, or if you’re in SQL Server Management Studio, when you, like, highlight a database or a server or something, and you press F7, that’s F like Frank 7, you get this new thing that pops up that gives you all sorts of neat details.

And one of my favorite things about it is, like, just to give an example, if you have a table with a bunch of indexes on it, and you want to script out all the indexes and just see what’s in there, if you hit F7 and you go to Object Explorer Details, you can actually multi-click stuff and right-click on everything that you’ve just multi-clicked, and you can hit Script, and you can get all the indexes rather than clicking on, like, one index at a time and scripting it.

It’s very convenient for many things. So, good job there, Andy Levy. You read my mind, or something. Next up is Andy Yoon with an actually very helpful stored procedure.

I would be terrified to run on some of my client environments. It is called SP Help Expand View. And what SP Help Expand View does is, if you have a view with a bunch of nested views in it, it’ll go through and find all the view references.

And there’s an optional mode where it will give you a count of, like, how many times things are referenced in the view. So, that’s a very, very cool thing to have. Like I said, I’d be a little afraid to run it and get, like, 90 columns of nested views back.

But if you’re feeling brave and bold out there or just particularly pioneering in one of the environments you work in, I would highly recommend using this to help yourself untangle the nastiness of nested views. So, well done, Andy.

Well done. All right. The next submission I had was Steve Jones pushing the boundaries of cutting-edge technology with testing the flat file wizard, in which Steve discovers that the flat file wizard can handle multiple delimiters, not just commas and fixed width, but also pipes and some other stuff.

And then at the very end of the video, Steve submits a pull request to the Microsoft Docs site where he makes some improvements to that. So, Steve really, like, pushed the envelope of not only data engineering, but also DevOps and, I don’t know, something else probably. But good job, Steve.

Steve, we now can use the flat file wizard with a bit more confidence, I hope, when we’re dealing with our flat files. All right. The next submission I got was from Deb, whose background, for some reason, looks nothing like Andy’s background.

At least last I heard, they’re still married. I hope I’m not messing anything up there. But she is asking some questions about stuff.

She wants to know how you consume content. She wants to know the type of content you prefer. She brings up some good points about videos where, like, it’s harder to watch videos. It’s harder to sit down and dedicate the time to, like, sit there and watch one thing and just, like, absorb that.

If you have something written, you can, like, go to it and come back to it and sort of, like, follow stuff around and, like, you know, take bites as you can. With videos, it’s a little bit harder to do that. I often find myself with, I mean, anything audio related, you know, it’s not just videos where there’s, like, a visual component.

But, like, I’ll find myself, like, I’ll put something, like, on a podcast. I’m like, oh, I really want to hear about this. And, of course, I’m sitting at my computer.

So I’m nip, dip, dip, dip, dip. And, like, you know, like, 45 minutes goes by and I realize I’ve absorbed nothing from over here. So valid points. I get it.

But, you know, also talks a bit about how the bots out there are stealing the words and not giving credit for the words. And, I don’t know, I think probably Sam Altman and, by extension, Microsoft owes all of us a very big royalty check for all the training material they’ve gotten from us. I’ve got a copyright on my blog posts.

I don’t know about you. I’m waiting for my royalty check in the mail. All right. Next up, our dear friend Mala, who is, oh, look out. Look at tiny Mala in the corner.

Why is Mala hiding? There’s Mala. Wonder why. Wonder why Mala is so small over there. But she has a great video about empathy being a technical skill.

Hopefully one that I learn someday. I would love to someday empath myself, empower myself with empathy or something like that. But good job there, Mala.

We could all stand to be a bit more empathetic in the world. Especially to people who have audio technical issues when writing a recording to invite people, or rather, recording a video to invite people to record videos. So, I guess the SQL community has a bunch of empathy in it.

All right. All right. And the final one was from our dear friend, Making Plans for Nigel, who got all dressed up, wore a Darling Data t-shirt. One of the few people who, I guess, didn’t donate the one they got for free from me at a conference to a homeless shelter.

So, thanks for that, Nigel. And recorded a lovely video from his backyard talking about his experiences with sort of like presenting and blogging and getting turned down from conference. It was an expansive video.

There are big feelings in this video. A lot of big feelings. But he is curious because he does put a question out there. Would you like to see more of these?

And, of course, Nigel, we would always like to see more of you and your fabulous backyard. All right. So, that’s the wrap-up. I hope that everyone out there who recorded a video enjoyed recording the video.

Maybe it will spark some more recording magic from you. I do like watching videos and getting a sense of the person behind all the content. But maybe I might be somewhat alone in that.

I don’t know. Anyway, thank you for watching. And I will, I don’t know. I don’t know when the next time I’m going to host a T-SQL Tuesday is. I think we’re rate limited to like once a year. So, you might not see me again until, what is it, 2026 is next year?

Good God. Someone stop this thing. Anyway, thank you for watching. Bye.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

A Little About Index Sort Order And The Order By Clause

Posted on April 14, 2025January 22, 2026 by Erik Darling

A Little About Index Sort Order And The Order By Clause

Video Summary

In this video, I delve into the fascinating world of SQL Server indexes and query plans, specifically focusing on a phenomenon known as “surprise sorts.” You’ll see how different indexing strategies can lead to unexpected sorting operations in your queries. I explore this concept by running a series of queries against the Stack Overflow database, demonstrating how the order of columns in an index impacts whether or not SQL Server needs to sort data during execution. This video is perfect for anyone looking to deepen their understanding of query optimization and indexing techniques, as it provides practical insights into avoiding unnecessary sorts that can impact performance. Whether you’re a seasoned DBA or just starting out, this content offers valuable lessons on how to craft more efficient queries and indexes.

Full Transcript

Look at you. You look great. You look great, smell great, everything about you. Great, great, great. Erik Darling here with Darling Data. Me and my pal Bats been chatting. We had an interesting client call where, you know, when I talk with people about SQL Server, SQL Server performance. We talk about a lot of stuff. stuff to do with queries and indexes and query plans and the way different things you do in your query and design your indexes has an effect on the query plan that you ultimately end up getting, which should also should all be at least fairly clear to my distinguished audience. But, you know, some people need a little bit more help and guidance than others. That is what I’m here for. So in this video, we’re going to talk about a sort prize. Now, now, keep in mind, this is not like a sort sort prize, like, hey, you won. Congratulations. You get a thing. Here is your honorarium of some variety. This is a surprise sort. And we’re not, this isn’t going to cause a big performance issue today. This is, we’re just going to look at how index, like the intersection of indexes and querying and how you can sometimes end up with a sort that you might not expect in your query plan. But before we do that, Bats would like to remind you that you can become a loyal paying member of the channel to say thank you for all of the hard, diligent work that I do producing this content. There is a link down in the video description there where Bats is pecking away, where you can, for as few as $4 a month, support your local SQL Server enthusiast. If you have perhaps engorged your Bats engorged your Bats with Pez candies, you’ve spent all your money there, you can do other things to support the channel. You can like, you can subscribe, you can comment. And if you would like to ask me questions that I will answer during my officially branded Bats Maru office hours, you can go to that link, which is also in the video description.

You can ask me questions that I will answer. If you need help with SQL Server beyond what you can get from mere YouTube videos, you can do all, you can hire me to do all sorts of things that people find useful. Bats is being a little, a little cranky today. I can do health checks, performance analysis, hands-on tuning of your SQL Servers, helping you with SQL Server performance emergencies, and of course, training your developers so that you don’t have those performance emergencies anymore. These are all things that, according to BeerGut Magazine, I excel far beyond anyone else in the world at. So, you are free to hire me to do all of these things. And you can rest peaceably with the knowledge that you have hired a BeerGut Magazine certified SQL Server consultant.

And as always, my rates are reasonable. If you would like to get some high quality, low cost SQL Server training from me and you old bats here, you can get all 24 hours of mine to fill your brain with for about $150 US dollars. And that will last for the rest of your life. All you have to do is use the coupon code right there at the link up there, which is also down in the video description. And gosh darn it, you can, you can start being as good at SQL Server as batsmaru.

SQL Saturday, New York City, 2025. That is this year. That is just a couple months away now. Coming up, May the 10th, with a performance tuning pre-con on May the 9th. I highly suggest you attend both. And you hang out with me and become my best friend. Maybe, maybe that’ll be the start of something beautiful. Who knows?

But without it in the way, let’s talk about surprise sorts, because they’re interesting things. Now, we’re going to run a query with a few different things going on in it. And we’re going to use this query and the index that we have available.

And then we’re going to, I’m going to show you what happens with a slightly different index. So we’ve got right now this index up here. Zoom it would be so kind as to zoom. There we go.

We’ve got this one here where reputation is in ascending order. Now, for those of you who are new to this whole thing, because the users table in the Stack Overflow database has a clustered, the important part here is clustered primary key, on the ID column.

The ID column is a hidden key column. It is a hidden key column because this is a non-unique index. You notice that there is a distinct lack of a word, the word unique, in here.

So because this is a non-unique index, the ID column, Zoom it would be so kind as to erase the squares instead of just having me click buttons mindlessly. The ID column is hanging over here as an additional key column. If the index, well, that D was a little aggressive, huh?

Let’s dot that I. If this were a unique index, then the ID column would be hiding, well, somewhere in this region is an included column. But since this is a non-unique clustered index, it is an additional key column, which means that this index is sorted by the reputation column first.

Right? So all of the values for reputation are sorted from one to whatever John Skeet is, one million and something. And for all of the duplicates, right?

Because we index all the data. So let’s say for the million or so people who have a reputation of one, the ID column is in order for that. But as soon as we go to reputation two, the ordering of ID resets and we start from whoever has the lowest ID to the highest ID within the next range of values.

So we have an index where reputation is in order and an index where ID is in order within all of those ranges of reputation. So we would expect to be able to order things, have an order by in the query that helps with all sorts of stuff, helps us avoid sorting data. Now, like I said before, this isn’t going to show a big performance issue.

This is just going to show you some behavioral stuff. So if I select the top 1,000 rows from the users table and I order by reputation descending, right? Just reputation descending on its own here.

Then we get a backwards scan of this index, right? Scan direction is backwards. But we don’t have to sort any data.

We have a top and we have a scan. We do not have a sort operator in our query plan. SQL Server did not have to acquire any additional memory grant in order to put this data in the order that we have asked for it. If we run these two queries, well, the reason why we might do something like this is because the reputation column is not unique.

Remember, we talked about that when we were talking about the index definition. And if we have any ties in the reputation column, we might want to add a tiebreaker in the form of this unique ID column to our query so that we have a way to uniquely identify the top 1,000. Otherwise, if we could get duplicate reputation, not replication, we could get sort of unexpected results ordering by a non-unique column.

So let’s run these two queries. And let’s look at what happens. Now, you’ll notice that for the execution plan where we order by reputation descending and ID ascending, we have a top-end sort.

In the query plan where we have reputation, so let’s put a little square around that here, make it obvious. This is reputation descending ID ascending. In this query, we have a top-end sort.

In this one, we just have a top. We are back to our original plan. Now, the reason for this is somewhat complicated or maybe not incredibly intuitive to folks out there.

And let’s try to explain it well. If you look at the properties of this index scan, you’ll notice that we do not have a direction on this one. If you look at what happens, there’s no, like, we didn’t have like a backwards scan.

If we look at this one, the backwards scan will be back. So this one has the backwards property. This one is an unordered scan of the table.

That’s why we have to sort this over here. And, of course, the sort operation is ordering by reputation descending and ID ascending. So the question is, why, when we sort by reputation descending and ID ascending, do we need to sort the data?

But when we sort by reputation descending and ID descending, we do not need to do that. Well, what it really comes down to, and if we look at the results over here, it might be a little bit more obvious. I do have to do a little bit of surgery on this to get both of these query panes to the right place.

And, of course, we need to get to around row 276 for this to be incredibly obvious or for this to start to become obvious. So let’s look at both of those. Oh, come on, SSMS.

You’re really making a fool out of me here. So 276 or so has the first duplicate that I could find in the results. Yes, I did scroll through and look for them.

So we have the reputation 160303 here. Right? And that’s the same for both of these. But you’ll notice that the order of the IDs is slightly different for them. In the top query where we’re ordering by ID ascending, we have 1-9-6-7-9 first and then 2-0-6-4-0-3 second.

In the bottom query for 1-6-0-3-0-3, we have ID in descending order. So we have 2-0-6-4-0-3 first and 1-9-6-7-9 second. Right?

So clearly those two values flipped. Now, the way to think about why we need to sort the data in the top plan but not in the second plan does come back to the execution plan. So, again, the properties of the scan with the sort does not have a direction on it.

Right? There is no ordered. When you look at the ordered attribute, it says false. So we just read through stuff and found it.

The reason for that is because imagine that you are the SQL Server engine. Right? And you are reading through the index over here backwards.

Right? So you’re reading. You’re doing a backwards scan. So let’s just pretend that, like, this is our B-tree. And over here is the, like, ascending end of our B-tree.

And over here is the descending end of our B-tree. So the backwards scan starts over here and starts reading things this way. Right?

So we’d be reading through this index in descending order. And then all of a sudden we would get to, like, we’d be, like, you know, we’re ordering by reputation descending. We’re reading through the index in descending order. And we’re trying to, like, you know, we want to order by ID ascending.

We would get to that, like, 160303 reputation. And then we would have to, like, do a U-turn and be, like, no, now we need to sort it this way. Right?

So SQL Server, like, it can’t really do that. Like, that’s not really how B-trees work. We can’t just, like, start sort, like, reading the index and then, like, backwards order this way and then be, like, ah, but then order the other column this way. Like, SQL Server is not, like, an advanced enough product or rather doesn’t have that advancement in the product in order to do that.

So in the second query where we are, we just, we can just straight up backwards scan the whole thing. ID is already going to be, like, we’re reading through the index backwards. So ID, the ID, the order of the IDs is also going to be backwards reading through that.

So as we’re reading through the descending order reputations, ID is also going to be in descending order. So SQL Server can just chug along that and have all the data straight in the order that it wants for both of those. So that’s why we don’t need to sort there.

Of course, you can write a query that would do that for you if you were to break things up a little bit and do some manual phase separation, as the smart folks out there say. And we were to get the top thousand reputation ordered by reputation descending in an inner query. And then join, remember, we have to get, like, we’re doing the group by here so we get our unique set of reputations back.

And then out here we join back to the users on that reputation. Then we can get the data that we want in the order that we want without sorting it. Right?

And the reason for that is because we hit that index twice. Right? Look at that. We scan into the index over here and we seek into the index here. When we scan the index here, we are doing that.

We are back to doing a backward scan. So we’re reading the data from over here this way rather than over here this way from largest to smallest rather than smallest to largest. And so we do the backward scan here.

We produce the top thousand rows. And then this index seek is going forward. So we’re reading through this index.

We’re joining on reputation. We start with the lowest reputation that came out of there. And we move to the highest reputation that came out of there. So we’re in forward order on this one. And we can avoid having to sort data at all in this query plan because we have two separate reads of the index that happen in two separate directions.

SQL Server could do this for us if it felt like doing a bunch of extra work in the query plan. But I understand a bit why it doesn’t. Now, of course, if we really cared about like we really wanted an index to do that for us, we could create this index because this would be reputation descending and ID ascending.

So what we would have here is an index that you don’t need to read backwards in order to get data from this end to this end because we have the highest stuff over here. And reputation is going to be an ascending order from there. So we can get this plan over here without a sort now.

So depending on how you need to return data with your queries, you might need to think about how you create your indexes and which direction you store your data in. Because sometimes if you need to mix sort like sort orders like this, like reputation descending, ID ascending, you might need to change the way that your indexes are set up or write really much more complicated queries in order to in order to avoid having to sort data. Now, of course, like the client issue that I was dealing with was a big sort and it was taking up a lot of memory and it wasn’t using a lot of that memory.

It was getting a very big memory grant, not really using it. And so we had to think of some clever ways around that problem. And that is what brings us here today is solving problems in clever ways.

We could do the query rewrite and leave the indexing alone or we could change the index. So we have choices as far as making things work. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something about, well, either indexes or forward scans or backward scans or some kind of scans. I don’t know.

There’s a lot of scanning going on in here. Anyway, maybe that’s my problem. Anyway, thank you for watching. I will see you in another video another time. Toodle-

Going Further

Equality, Sort, Range Indexing In SQL Server – When It Doesn’t Work

Posted on April 11, 2025January 22, 2026 by Erik Darling

Equality, Sort, Range Indexing In SQL Server – When It Doesn’t Work

Video Summary

In this video, I delve into the intricacies of equality sort range indexes, specifically when they might not be as beneficial as initially thought. Building on yesterday’s discussion, I demonstrate how these indexes can struggle when your range predicate is highly selective and your ordering elements are poorly aligned with query requirements. By running through several queries and their corresponding execution plans, I illustrate the challenges faced when trying to optimize performance in such scenarios. The video also includes tips for improving query performance, such as using index hints and considering alternative indexing strategies that better align with specific query patterns.

Full Transcript

Your best friend in the entire world, Erik Darling here, to talk to you further about a subject that we broached in yesterday’s video, which is the Equality Sort Range Index. In yesterday’s video, I did say that there are situations where it’s not going to be as great of an idea as it is in others. And we’re going to, we, I owe you at least something for that. So, to sort of generalize a little bit, the problem with this style of indexing comes when your, let’s see, an easy way to put this, when your range predicate is far more selective than your Equality predicate and your ordering element or elements are in a bad order for the way you want to search for things.

It’ll make a lot more sense when I actually show you the demo. So, for now, let’s just, let’s just keep in mind what the topic is going to be and be amazed and astounded and just wowed right out of the seat of our pants when, when I show you what’s going on. But before that, of course, I like doing this stuff.

I like doing this stuff even more when people sign up for memberships. So, if you would like to do that, there’s a link right down in the video description. And for as little as four bucks a month, you can say, hey Eric, thanks for doing all this.

It looks like a lot of work. You’re a real sport. If you do not have four bucks a month, perhaps you have significantly increased your Pez dispenser collection, used eBay for its original purpose, and you just don’t have four bucks, you can do other things that help me become a bigger, better person.

You can like, you can comment, you can subscribe. And if you want to ask questions, fancy little pinky out questions privately that I will answer publicly on this very YouTube channel, you can go to the link above then, which is also in the video description, and you can ask your questions there.

And I will answer them five at a time. Again, I’ve got a bit of a backlog on these, so we’re going to record some of those very soon. If you need help with SQL Server, if asking a question anonymously that gets answered non-anonymously online, you can hire me as a consultant to do all sorts of things with your SQL Server to improve performance.

I do all of these things and more. And as always, my rates are reasonable. If you would like to get some training content from me, carrying on my fine tradition of rate reasonability, you can, again, video description has the fully assembled link, but if you feel like typing, you can go to training.erikdarling.com and punch in the discount code SPRINGCLEANING and you can get 75% off any of my training videos.

Fun stuff there. SQL Saturday, New York City, 2025. That is this year. That is, at this point, let’s, uh, uh, I mean like, really, like, two months away at this point.

So, I, if you’re speaking at them, gosh, I hope you practiced. And, uh, as always, I will see you there. Uh, bright and, bright and oily.

But with all that out of the way, let’s, uh, let’s talk about when this equality sort range index thing doesn’t work out so well. So, uh, because I felt the format of yesterday’s video worked pretty well, um, I have pre-created some indexes and I’m gonna run some queries using index hints to show you what happens when these queries interact with those different indexes.

Uh, so let’s run these two queries. And I’ve got query plans enabled because, gosh darn it, I’ve been practicing. Don’t wanna, don’t wanna disappoint anyone with, uh, unpracticed SQL Server demos.

And you might notice that these, at least, well, one of these queries is taking quite a while to execute. And, uh, it’s gonna continue to take quite a while to execute. So, what these are doing, and the, the difference between these two queries is just in the ordering.

Uh, this one is, of course, ordering by the ID column in ascending order. And this one is ordering by the ID column in descending order. So, for this first query, uh, what it, what’s worth noting here are a couple of things.

Uh, one, we’re gonna pretend we don’t have any useful indexes at first. And I’m gonna show you what happens when either you don’t, you don’t have any useful indexes, or for whatever reason, SQL Server does not choose some useful index that you have.

Uh, let’s just put, like, you could, you could substitute this ID column for like a select star, or select a bunch of columns type thing. And SQL Server might cost using a narrower index out of, uh, out of the equation, because it doesn’t want to do a whole mess of lookups.

All right? So, just bear with me here for a moment. Um, we are forcing the clustered index via this index hint. And we are looking for, in the votes table, vote type ID equals two.

Vote type ID equals two has about 37 million rows associated with it. That is the almighty upvote in Stack Overflow land. And the other predicate that we have on this table is where creation date is between 2013-1201 and 2013-1231.

Now, since this is the Stack Overflow 2013 database, the world ended right here. There are no, there are no rows after this. So, this is the last month of data in the table.

The problem that we run into here is that SQL Server can, like, find 37 million rows for this pretty easily. But finding the top 10 rows, right? Because we’re doing fetch next 10 rows only, offset zero rows, ordered by ID ascending.

It, you have to go through a lot of the table before you get to creation dates that meet this predicate. So, finding the top 10 ordered by this is what’s really tough. If we look at the execution plans for these, this first one takes 20 seconds.

Two, zero, 20, right? If we look at the clustered index scan, the number of rows that got read is this many, right? 5, 1, 4, 0, 1, 7, 1, 5.

That is an 8-digit number of rows. That’s a lot of rows. The number of rows in the table is 5, 2, 9, 2, 8, 7, 0, 0. That is also an 8-digit number of rows.

But if you notice, they both start with the same number, 5. And only once you get over to the second number, there’s only like maybe like a million and a half or so fewer rows that get read than there are rows in the table. And of course, since this happens awful in the single-threaded, and we have this top just like, gimme rows, gimme rows, gimme rows, gimme rows, gimme rows, that takes a whole long time, right?

It’s not a good thing. You can find stuff, you can find partially reasons for stuff like this in your query plans. If you look at the properties of an index access operator in a query plan that has like top or offset fetch, and you’ll see this thing in here, estimated rows without row goal, that’s going to be a sign that SQL Server said, well, you know, I think I have a different estimate in mind.

If we didn’t have that top 10 in there, I would have to read all of this. But with the top 10, I don’t have to read nearly as many. SQL Server is just like, you know, I think I can get away with a lot less than that, right?

So, fine there, right? All good. But then down here, notice that we do far less work, right? This thing spits out quite a large number of rows there.

This one spits out, oh, whoa, what just happened there? Developer PowerShell. That was a weird button. Let’s never hit that again. Let’s never touch that button again. Come on, there we go.

Zoom it. All right. So, for this one, we only had to read 18 rows to find anything that we cared about. So, that is, of course, far fewer, right? That is far fewer rows than you would want to deal with there.

Now, of course, Paul White, who is the most useful human being on the planet. Let’s just take a moment here to acknowledge that Paul White is the single most useful human being, maybe, that has ever existed. And you can’t spell Paul without the U for the most useful human.

We’ll workshop that later. Let’s just move on from that. That didn’t go so well. You can’t spell useful without Paul. Nah, screw it. Anyway, you can somewhat improve these queries.

And I’ll try to remember because, gosh darn it, I know how much you care to put this link, a tale of two index hints over at the much-loved SQL.Kiwi website in the video description. So, if you use a weird index hint and a tab lock hint, you can improve the performance of these queries quite dramatically.

These will drop down to about 1.3 seconds a piece. But now we have this sort in the query plan, right? Because SQL Server, when you use index zero in the tab lock hint, Paul explains this much better at his site.

But basically, like, you no longer do an ordered scan of this data, and then you have to sort this data. So, it does look a little bit funny that, like, we’re selecting from the table, and now all of a sudden we have to sort data to put it in order by the column of the primary clustered key that’s already in order by it.

So, that’s a little weird. But anyway, these do get slightly worse if we take out the tab lock hint, which I find quite amusing. And again, which if you go to the Paul’s post, you will find quite a bit of detail on this.

But these do go from 1.3 seconds a piece to almost 1.8 seconds a piece. So, that tab lock hint is, let’s just say, strictly necessary for the full performance benefits here. So, slight digression.

Anyway, let’s talk about why that equality sort range index does not help a query like this. So, this is my index v0, and I’ve got vote type id, id, and creation date. These are our equality sort range predicates, just like we talked about yesterday.

And both of these queries are hinted to find, to use that index. Now, this will help performance somewhat generally, right? Like, that first query, the first time we ran this using the clustered index, it took us 20 seconds to locate rows in there, right?

It was not a good time. We, you know, we beefed it on that one. We did not do well.

With that index, and with that index hinted, we save about five seconds. So, this is, again, not great, right? We did not, like, see the benefit from using that index methodology for this query the way that we do, the way that we did for the query yesterday.

And this is because, again, we need to seek through quite a number of rows. Even, like, we can seek to these rows now, and we can have this thing in order. But to evaluate this predicate on creation date, we still have to do a lot of extra work, right?

So, like, with that query in place, like, yesterday, this worked great. We did a seek. We had our data in order.

There’s no sort in this query plan or this query plan, I promise. But then applying this much narrower predicate on the creation date column completely screws things up, right? Like, we get the 37 million rows just by seeking to vote type ID 2.

But then after we find those 37 million rows, we have to go apply this predicate to all of them, right? It’s no fun, right? It’s no good at all.

So, the second query with ID in descending order does just fine with this, right? So, like, well, it’s not totally, this is, like, that funny intersection of, like, how indexes and queries work together. If your query demands rows in the ID with ascending order and your predicate is, like, something that’s way deep into the table, you’re going to have a bad time finding that data with this type of index.

The type of index that would work better if you need things in ascending order here would be an index like this that leads with creation date. And I’m going to show you two forms of this index. This one is creation date, then vote type ID, then ID.

The second one is going to be creation date ID, vote type ID. But the point for me, the point of me showing you both of those is that this sort of breaks the pattern that the equality sort range thing fixes. Because when you put the range stuff at the beginning of the index, even if there’s an equality predicate in the middle, like, it breaks that.

Because creation date is still the primary sorting and vote type ID is still only sorted after that. So, like, you don’t have the ID column in a useful order for this one. So, if we look at the query plans for this, these are both hinted to use the V1 index.

These will both be fast enough to find the rows that we care about. We do have a sort in the query plan, which, you know, was part of the thing that we wanted to get rid of with the ESR, the quality sort range index methodology, was having a sort in the plan. But in this case, we get the number of rows down into a pretty small batch.

So, the sort isn’t very painful in either of these. Both of these take just a little under 100 milliseconds. This is the second index in there that I promised I’d show you.

Creation date ID and then vote type ID. So, now we have, like, you know, range, sort, equality. But this doesn’t really make that much of a difference because, like, it’s just for, like, this specific query, you just do much better to be able to locate that narrow range and then look at vote type ID no matter where, almost no matter where it is in the index.

You can probably have it as an included column and it wouldn’t make any difference. So, if you run this is so, both of these will, both of these queries perform just about the same as with the index in slightly different order. So, like, swapping ID and vote type ID doesn’t make a big difference here.

One thing that you might want to consider is, like, just in general, like, the ordering of your columns and some columns, like, kind of share ordering. So, like, in this case, if you really wanted to get rid of the sort, you might consider ordering by creation date. In this case, for the votes table, the creation date column, like, like, obviously that’s for a new row that gets inserted.

When a new row gets inserted, the ID column, which is an identity, also increments. So, every creation date and every ID in this table is going to be higher than the one before it. You might have columns that similarly share ordering and that you might be able to rely on for this.

Now, just because of the way votes get inserted here, there aren’t going to be any duplicates for creation date. Right? Because, like, like, you’re just going to not have two things show up at the exact same time.

So, if you really wanted to get rid of the sort, you could run your query and instead order by creation date and just have a much simpler query plan overall. So, you might not always be able to get away with that, but in certain cases where you can, this might be a good thing to do. If you needed to have a tiebreaker in there, you would want to use this v2 index on creation date and then ID.

So, you could say, like, order by v.creation date, v.oops, ha ha, typing in demos. Look at this silly man go. So, if you needed the tiebreaker, like, this would be the better indexing strategy for these two, right?

Because you would have order by creation date, order by ID, and this index would be able to fully support these things. So, the equality sort range indexing, again, it’s great for a lot of situations, but for situations where your range is much narrower and sort of like, let’s just, like, to make it easy to physically sort of understand it. Like, the range is much narrower and towards the end of the table, and you’re ordering by stuff in ascending order.

Like, you have to read through a lot more of the table to get down to the stuff that you care about down here in order to find those rows. So, it’s not always going to work out perfectly for every query, but for some other, for some types of queries, it works amazingly as is. For other types of queries, you might have to either change your ordering to better, like, locate data towards the end of the table that you care about, or to sort by a different column in the table with the ES, with, like, you know, with some sort of index in place that helps you put this stuff in order without having to sort.

So, sort of a complex situation there, but one that is worth examining and one worth talking about. I do hope you enjoyed yourselves, and I hope you learned something, and with that out of the way, I don’t know, I’ll talk about some other fun stuff. Well, we’ll see what it is when we get there, though, won’t we?

Alright. Anyway, thank you for watching.

Going Further

Equality, Sort, Range Indexing In SQL Server – When It Works

Posted on April 10, 2025January 22, 2026 by Erik Darling

Equality, Sort, Range Indexing In SQL Server – When It Works

Video Summary

In this video, I delve into a fascinating indexing methodology that I recently discovered and find incredibly useful for improving query performance in SQL Server databases. It’s not about creating new types of indexes but rather following an effective indexing strategy known as the Equality Sort Range (ESR) index. I explain how to structure your indexes by placing equality predicates first, followed by sorting elements, and ending with inequality predicates. This approach ensures that data is ordered efficiently for both seeking and ordering operations, leading to faster query execution times. Through practical examples, I demonstrate how this indexing method can significantly enhance performance compared to traditional index creation techniques.

Full Transcript

Erik Darling here. Your pal Erik Darling here with Darling Data. In today’s video, we’re going to discuss a term that I recently discovered, which is a wonderful way of phrasing an index methodology. Not a specific type of index, of course, not like a brand new style of index that you can create in your database, but just an indexing methodology that you can follow. generally used to improve query performance quite a bit. And that is the Equality Sort Range Index. It rolls right off the tongue, just like the POC index, the partition over covering index that is so very popular when people are talking about improving the performance of windowing functions. So we’re going to call this the ESR index. And we’re going to look at an example of how you can use this indexing methodology to improve your query performance. Yay!

We’re all excited. If you like this video content and you would like to be a supportive member of the audience, you can sign up for a membership using the link down below in the old video description. And you can, I don’t know, contribute to me being a happier, well, probably not healthier person, but you can contribute to me being a happier person that way. If you do not have a the dinero to contribute to that, to the happiness fund in that regard, you can always like comment and subscribe. And if you would like to ask questions of me privately that I will answer publicly, you can use that link, which is also in the video description. Coincidentally, many useful things down there to put your question into my magical Excel file that I will read from. If you need help with SQL Server, if you are struggling with performance, reliability, scalability, application, blah, blah, blah, blah, you can hire me as a consultant to do many useful things for you. Health checks, performance analysis, hands-on query and index tuning, all that other stuff that needs tuning, dealing with performance emergencies, and of course, training your developers so that performance emergencies are a thing of the past.

All worthy goals when it comes to SQL Server. If you would like some training, boy, do I have it. My pockets are full of it. You can get all about 24 hours worth of training from my pockets for 75% off. That’s the link, that’s the discount code. And like everything else useful in the world, the video description contains a fully assembled version for you to click on. Upcoming events, we have SQL Saturday, New York City, 2025. That’s this year. It’s hard to believe. Coming up May the 10th with a pre-con by Andreas Walter on May the 9th. And that’s about SQL Server performance monitoring and tuning and stuff. So we’re all looking forward to that. I’ll be there both days serving you lunch, bringing you, bringing you cookies and coffee and whatnot.

And so you can come hang out. Give me a hug. I don’t know, whatever you’re into. But with that out of the way, let’s party. Let’s do this thing. Let’s do this thing like we care about it. So I, for years, have been talking about this thing that I do when I’m tuning queries and indexes. But I had never, I always talked, it was always very clunky in my head. It was just like, but it’s like, you do the thing and then the other thing and then you care about this thing.

And then I saw a fellow named Frank Pachaud, I apologize, Frank, if I pronounce your name wrong, on LinkedIn talking, I guess he started a job with MongoDB recently. We’ll all forgive him for that. We’ll have a seance for Frank. We’ll talk to him in the database hell. I’m kidding. Where he posted a link to this thing in the MongoDB dots, where they talk about the equality sort range rule for indexing.

And I thought, wow, that’s a thing that I didn’t have a good name for. And so we are going to start referring to him. We’re going to meme this into existence for SQL Server, the ESR index, that is equality sort range. And we’re going to show some examples of that. Now, I’ve got a query down below and without any indexes, starting with no indexes on the POST table, SQL Server gives us a missing index request.

The missing index request that it gives us leads with POST type ID and then has last activity date as a second key column. And then in the includes, we have score and then view count. So the missing index request in SQL Server, as far as key columns go, are very WHERE clause centric.

If you look at the WHERE clause that we have here, we have WHERE POST type ID equals 4 and WHERE LAST activity date is greater than or equal to 2012-01-01. So SQL Server puts the equality predicate first and the inequality predicate second. And it doesn’t give a lot of thought to the fact that we are ordering by score over here, right?

Doesn’t really care about that. But we’re also selecting view count, right? So view count being in the includes, that’s fine.

But score being in the includes, that is not so fine. Because columns in the include of an index are not sorted or ordered in any useful way for, you know, either seeking to values or for helping with presentation or operator-dependent order buys. Like I’ve talked about in many other videos, things like merge joins and stream aggregates require sorted data.

And if you ask for data in an order that is not supported by an index, SQL Server has to put a sort operator into the query plan and break out its tiny little baby fingers and magically put your data in the presentation order that you have asked for. Other things like windowing functions also do pretty well with ordered data. So what we’re going to do is look at examples of these two queries using this index.

This is index P0. Both of these queries are hinted to use this index, mostly for a little bit of compactness here. We do have a few things to talk about.

And so I pre-created three or four indexes and I’ve hinted queries in each section to use those indexes so that there’s no weird overlap and oops, this query used this index this time. Sorry, whatever. So because that gets annoying.

So we’re going to run these two queries. And the first one is going to be pretty quick. And the second one is going to be a little bit less quick. And I’ll be honest with you, I am not fully used to SSMS 21 and where it puts the little grappling hooks for us to move things around on the screen with.

So you’ll have to bear with me while I acquaint myself with this wonderful 64-bit program. But for the first query, this is fast enough, right? This four milliseconds, six milliseconds total, great.

Like, no problem. Like, all easy peasy there. This one, not so great, right? We do an index seek. Okay, 284 milliseconds. But then we hit this sort and we spill a little bit.

Not so much that I’m, like, worried about it. And then we, of course, end up taking nearly a very devilish number of milliseconds to complete this query. So this isn’t really great.

This isn’t really an awesome scenario for the second query. But there is a way to make both of these fast. Now, a lot of people might be tempted to create an index that looks like this.

Where score is the leading key column. Because then we will always have score in descending order. The problem this presents us as query tuners is that with score is the leading column in the index.

It sort of acts as a gatekeeper to the other columns. So score is in order. But post type ID and last activity date are not in a useful order after this for searching.

So we sort of have this, like, blocker here that prevents us from seeking into the index for the values that we care about. So this is index P1. Both of these indexes, both of these queries are hinted to use index P1.

And if we run these, we’re going to get sort of not great performance out of either one. This query up above that took, well, like, a few milliseconds before is now what? Is that the right one?

Let me just go double check, make sure I didn’t mess anything up. No. All right. So, yeah. This one up here that used to be really fast took 1.5 seconds. This one down here, this one actually got a little bit better, right?

Even though we end up scanning this whole index, it got a little bit faster than last time. Mostly because we didn’t have the sort spill on this one, right? So this one did not take a devilish amount of time before.

This one just took a long time to find all the, scan through and find all the data that we care about. So what I’m going to show you now is the ESR style of index that I talked about in the video introduction.

We are going to lead with the equality predicate. We are going to follow that up with the sorting element in our query. And we are going to put the last activity date as the final key column. We’re still just going to include view count because view count, we don’t have a predicate.

We’re not in our where clauses, not in our order by. We’re not joining on it or anything. So there’s really not a lot of sense in there aside from just putting it in the included columns.

So let’s look at query performance with these two. And let’s see if this actually gets faster using our ESR index. And it does, right?

So this is about, this is what I want my query to look like. We have two very, very fast index seeks into our P2 index. This is our equality sort range index.

And we don’t need to sort data because the data is in order for us. Now, I just want to show you what the seek predicate looks like. This is going to be identical for both of these aside from the actual values in them.

This one’s four, this one’s 2012, the other one’s 2011 and one or something like that. But the important thing here is that we are able to seek in the index to where post type ID equals four.

And then we can take advantage of how B-trees store data, which is that like everything is primarily in this index ordered by post type ID. And then once we seek to any post type ID, like where post type ID equals something, all of the scores will be in descending order for that particular post type ID.

So we don’t, like, we’re not saying where post type ID is in four or five or greater than four, because that would mess things up because we would be crossing boundaries. We would be crossing post type ID boundaries to different post type IDs.

And the score column order would reset for each post type ID. So that wouldn’t do us any good. But for an equality predicate, where we just find all the rows for one thing, all of the data in the index is ordered within that one thing.

And that just gives us a quite opportune index to apply the C predicate, keep the data in order that we need for score, and then apply a residual predicate on last activity date to finish filtering out rows that don’t matter to us.

Right. And just because of the way indexes, B tree indexes work, we don’t even necessarily need view last activity date as a key column.

If for whatever reason you just wanted to have it as an included column, you would get the exact same execution plan. So just to show you what I mean, I’ve created a fourth index because we started our index numbering at zero because we’re nerds.

I put last activity date as a second include column here. Right. So it’s not even the first included column, but this is mostly just to get across the point that include column order doesn’t matter much.

If we run these two queries, we will get identical performance as the last query and our query plans will look just about exactly the same as well. Here is our index seek that goes directly to post type ID four.

And here is our residual predicate on the last activity date column. Now, the reason why this doesn’t matter so much here is because range predicate range or rather inequality predicates greater than greater than equal to less than less than equal to not equal to all that stuff is not no equality predicates and is no predicates are boom, you equal this.

like they often end up as residual predicates in queries anyway. Not all the time. Sometimes. Sometimes it happens. Sometimes it doesn’t.

Sometimes you have a seek and a residual predicate. But this is just to show you that the final key column in this case does not really have much impact on final query performance.

So when you’re looking at when you’re trying to tune queries that do something like this, they can, of course, be more complicated than this. But you can always get indexing for these to really help query performance when you embrace the equality sort range paradigm of index creation.

So you want to make sure if you have equality predicates, you want those to be the leading key or key columns in the index. You want your ordering elements to come after the equality predicates.

And you want any inequality predicates to come third in the index or come after the ordering elements in the index. Not necessarily third, of course.

Right? Could be tenth. I don’t know. At that point, you really start to see the benefit of included columns in indexes because you don’t have to have 13 key columns in order to support this sort of thing. So you could put your inequality predicates either as key columns after the sorting elements or in the includes because they will generally have less impact on things after that.

Now, there are always going to be some exceptions to the rule. If your inequality predicates are very, very selective, you might not want to have them as includes. If you’re seeking to like 10 billion rows and you have to apply a range predicate to like, you know, 9 billion rows afterwards, it’s maybe not as valuable.

But once you’re dealing with tables that large anyway, you should be using columnstore indexes. And I don’t know, I’m probably thinking about the meaning of deeper meanings in life because you might be a little, you might be dealing with many other things.

Also, you don’t see a lot of top 5,000 order by queries and that sort of situation. But anyway, thank you for watching. I hope you enjoyed yourselves.

I hope that you will embrace the equality sort range indexing methodology. I hope that you will join me in memeing this into widespread existence in SQL Server. And I will see you in another video where I will hopefully get back on track talking about store procedure stuff.

Really trying to work out some good demos for the temporary objects stuff that I want to talk about there. That might end up being more than one video because there’s a lot to say. But anyway, thank you for watching.

And I will see you in another video, I don’t know, shortly or longly or I’m really just not sure at this point. Well, the fates will decide. Anyway, thank you.

Going Further

SQL Server Books I Recommend

Posted on April 9, 2025January 22, 2026 by Erik Darling

SQL Server Books I Recommend

Head on over to the books page!

Video Summary

In this video, I delve into my extensive collection of SQL Server books, sharing insights and recommendations based on years of experience in the field. From the foundational works like Ken Henderson’s “The Guru’s Guide to SQL Server Architecture” and “Gone But Not Forgotten,” which provide a deep dive into core concepts and are surprisingly relevant even today, to more recent titles such as Itzik Ben-Gan’s “T-SQL Fundamentals” and Louis Davidson’s “Pro-SQL Server Relational Database Design and Implementation,” I cover a wide range of topics that will benefit both beginners and seasoned professionals. Each book offers unique perspectives and practical knowledge, contributing incrementally to one’s understanding of SQL Server. Whether you’re looking for fundamental architecture insights or advanced troubleshooting techniques, there’s something in this collection for everyone.

Full Transcript

Erik Darling here with Darling Data. In today’s video, because I have gotten no fewer than like 10,000 questions on my office hours thing about SQL Server books or database books, we’re going to talk through my stack of books. And I have even put together a website, a page on my website that lists all of the books that I recommend. They are, of course, Amazon affiliate links because I spent 10 minutes putting together this list. And I figure over the span of time, the 53 cents that I’ll make from you people clicking these links and maybe buying things will be adequate compensation for my effort there. So let’s talk about books. The first one, and this is a granddaddy book. This is The Guru’s Guide to SQL Server Architecture and Architecture and Internals by Ken Henderson by Ken Henderson. This is by Ken Henderson, who’s dead. So RIP Ken. This one has a CD-ROM in it that is unopened, which I’m pretty psyched about. I did have to get a lot of these books second-hand. And I think one of my favorite things about buying second-hand books is the weird stuff that you find in them. I bought one sort of recently, unrelated to SQL Server, that had like a laminated, like kids-made bookmark, that said, like, Happy Father’s Day 2008. And my wife found it and she got kind of freaked out, but it was all explained. Anyway, this is a very good book. Old, but well worth it because you learn a lot from this stuff. This fills in a lot of fundamental knowledge stuff that a lot of people are missing.

We’re going to stick with Ken Henderson for a couple more here because Gone But Not Forgotten and certainly Not Gone Without Leaving is his mark on the world. We have the Guru’s Guide to Transact SQL, which covers many great and interesting T-SQL concepts and conventions. Granted, this is, again, old, but very useful. The one thing that is not in here, aside from a CD-ROM which is missing, is anything about API cursors, which I recently had some fun with and I will probably do a video about because, you know, that’s what I do. I have fun with things and I make videos about them for you. The third and final Ken Henderson book, which also has an unopened CD-ROM in it, is the Guru’s, well, sorry, there’s a sticker over there.

The Guru’s Guide to SQL Server, the Guru’s Guide to SQL Server, Store Procedures, XML and HTML. Granted, I would probably not recommend doing much HTML with SQL Server, but XML still around to this day. This of course predates JSON, so we can’t really go into detail on that. But everything else in there is absolutely wonderful. Moving on now from the Ken Henderson wing of my library into the, well, actually, no. This is, this is, there’s one more from Ken Henderson before we move on to other ones. SQL Server 2005 Practical Troubleshooting.

Well, sorry, this is edited by Ken Henderson. Who is the author on this? This might have like 17 authors. I don’t know. Let’s see. Let’s open this up. Edited by Ken Henderson. Let’s see what we got here. We got a table of contents. This is going to go on for a while. About the authors. All right. Okay, cool. Well, let me, there are, there are a number of notable people in here. Some of them I haven’t heard of, but we have August Hill.

We have Cesar Galindo Ligari, who is still on the SQL Server Optimizer team. Very smart fella. Ken Henderson, of course. Samir Tajani. Santeri. Oh boy. Vudelenin. Slava Ox. Hey, my pal Slava Ox. Wee Zhao. Bart Duncan. Great SQL Server blogger from back in the day. And of course, Bob Ward from back when he was in, it’s going to be hard for me to show you this, but actually, yeah, that’s not working out well at all.

But this is Bob Ward when he was still in Microsoft Customer Support Services. And Cindy Gross is the final one noted here. So that, that, this is a collection of authors. So edited by Ken Henderson. Sort of a slightly weird thing there. Anyway, now we’re going to move on to the Kaylin Delaney et al. wing of my library. We’re going to start off with Inside SQL Server 2005 Query Tuning and Optimization. Now I know what you’re going to say. Query tuning in 2005 is totally different than it was today. It’s not. A lot of the same stuff still applies. We just have some new tools and some new techniques, but there is a lot of fantastic information in here for folks who need to learn fundamentals and who need to maybe see just how similar and just how consistent the concepts in database query tuning and optimization are.

This one is maybe not so important to query tuning, but it is, it is a cool book. This is Microsoft Inside the Storage Engine for SQL Server 2005. Now I know that the storage engine has had many changes and many things added to it and stuff like that, but there is still a lot of very good foundational knowledge in this book. Next up, we have Microsoft SQL Server 2008 internals. Internals knowledge, very good stuff to have. Even in 2008, there’s good stuff to learn in here. One thing that you’re going to find across all of these books is that you’re going to pick up something new in all of them, right? I don’t mean new in like, oh, this is like, like, obviously we’re up to SQL Server 2022. There have been whispers of SQL Server 2025 already. But one thing that you’re going to get across all of these is incremental. There’s going to be some stuff in some books that you might not see in the other books.

There’s going to be some of these books that you might not see in the other books. And there’s going to be a knowledge accumulation for you as you go across your learning journey. And then this is probably the last of the great SQL Server internals books, SQL Server 2012 internals. So the 2008 book was, had contributors, Paul S. Randall, Kimberly L. Tripp, Connor Cunningham, Adam Mechanic. All right, a lot of good stuff there.

And this one here, we have, we still have Connor Cunningham. We got John Cahias. We got Paul Randall. We got Bob Beauchemin. We got all sorts of smart people contributing to these books. Now, we’re going to move on to the Itzik Ben-Gan wing of my library.

And we are going to see T-SQL Fundamentals. This is the fourth edition. Itzik, before he went into semi-retired hermit phase, did update this. This is the latest edition of that. Continuing on with the Itzik wing of my library, we have T-SQL Querying.

Thick book, good book. I’ve had this one since, oh, it came out in about 20, what was it, 2015, I think, 20, somewhere there. This one is Itzik, Dajan Sarka, Smartfella, Adam Mechanic, Kevin Farley. Kevin Farley, who recently retired from Microsoft. Good for him.

Next up, a slightly more recent book. We have Pro-SQL Server Relational Database Design and Implementation by Louis Davidson. Louis was kind enough to have me on the Redgate Simple Talks podcast recently.

If you haven’t listened to that podcast generally, or at least my episode, I can highly recommend you go do that. And the final book that I have here is by a fella named Dimitri Karatkevich, SQL Server Advanced Troubleshooting and Performance Tuning. I do like this book quite a bit. There was actually even something in here that he discussed.

I forget exactly what it was at this point. There was something with the DMV query that I thought was cool. I actually realized that I didn’t have that in SP pressure detector, so I added it in. I think I even have his name in the pull request for it, but I forget a little bit.

But anyway, this is a very good book. And I think one of the things that I liked best about this book is, you know, a lot of the times that I’m reading something, I have, like, the stuff that I would think and say when I’m talking about something.

And, like, he would be, like, talking about a topic, and it would, like, you know, like, he’d, like, you know, make a point about something. And then I’d be like, yeah, but, like, this other thing that you have to take into account with it. And then the next sentence would be like, but of course you must consider.

And I was like, yes, this is a very good book. So if you are a bookish person, those are the 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 books about SQL Server that I generally recommend. There are a couple notable books that are not on this list because they are not about SQL Server specifically that are also good.

They are Database Reliability Engineering and Designing Data Intensive Applications. But you can get all of, you can get the full list with Amazon links to purchase these books on my website. That’s going to be erikdarling.com slash books.

I will have the link to my site in the video description here. And you can go buy them and you can go learn from them. And maybe, since you’re most likely buying used books, maybe you can find some cool artifacts from the sands of time in there.

But anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

And I hope that you will go buy some books if that is your preferred vehicle for learning about things. So anyway, thank you for watching.

Going Further

The Weird World Of SQL Server API Cursors #tsql2sday

Posted on April 8, 2025January 22, 2026 by Erik Darling

The Weird World Of SQL Server API Cursors #tsql2sday

Thanks for watching!

Video Summary

In this video, I delve into the fascinating yet somewhat obscure topic of API cursors in SQL Server, exploring their unique capabilities and usage scenarios that go beyond the typical row-by-row processing. While most people might cringe at the mention of cursors due to common misconceptions or past frustrations, I aim to demystify these powerful tools by walking you through a practical example where we use an API cursor to batch update multiple rows efficiently. This approach not only showcases the flexibility and power of SQL Server but also highlights the importance of understanding its less commonly known features for advanced database management tasks.

Full Transcript

Erik Darling here with Darling Data. In today’s video, we’re going to get into the weird, wild world of API cursors. Now, this is something that most people who use SQL Server have never heard of and probably have never used. If you’re the type of person who just has the standard Pavlovian response, seeing a cursor declared or used anywhere and complains and gripes about it, this is not the video for you. I don’t like you much. So there’s that. We’re going to talk today about how API cursors can be used in ways to traverse more than just a row at a time. This is not a row by agonizing row thing. We can actually process, select, select, modify multiple rows at a time using API cursors, but we’re going to have to do a little, we’re going to have to do a little bit of digging into the vaults to, to understand what’s going on here. So with that out of the way, if you like this content and you would like to support my efforts to bring you high quality SQL Server content like this, you can sign up for a channel membership link right there.

down in the video description. If you are too poor because you spent all your money griping about cursors in SQL Server to some LLM trying to get it to write a blog post for you. Well, there are the ways to support the channel. You can like, you can comment, you can subscribe, and you can ask me questions privately that I will answer publicly during my office hours episodes. If you need help with SQL Server, performance help with SQL Server, I can do all this stuff. And of course, as mentioned is rated by beer gut magazine to be the best SQL Server consultant in the world outside of New Zealand. Today’s video will of course, help establish why outside of New Zealand is particularly important to that distinction. If you would like to get some very high quality, very low cost SQL Server training content, you can get all 24 hours of mine for about 150 US dollars. And you get that for life. There is no return fee on that. The link is up there. The discount code is there, of course, all fully assembled for you down in the video description.

Of course, if you would like to hang out in person. And I don’t know, high five, take selfies, sign autographs. I don’t know, ask me how to set Mac stop. You can of course come to SQL Saturday New York City 2025 taking place on May the 10th in lovely Times Square Manhattan at the Microsoft offices. I believe it’s 11 Times Square. If you go to my website, there’s a link up in the corner. All that stuff. Go there. If you go there. If you go there. If you go there. If you don’t know what my website is, well, geez, that’s a scary thought. It’s a scary thought. Anyway, let’s talk about API cursors. So before I show you my thing, what I want to show you is where I learned about API cursors from and sort of like why I got interested in them. Because I think they’re just bizarrely interesting things. So Paul White from New Zealand has a couple blog posts or not a couple blog posts, has a couple Q&As on the database administrator stack exchange site. Now, I want you to pay careful attention. I’m not logged in up here, right? There is a login prompt. So I don’t want you to think that like I haven’t, I haven’t upvoted these questions because when I’m logged in, you absolutely will see.

That these things have been upvoted to the nth degree. I will put the links to these questions in the video description, hopefully, if I remember. We’ll see. But anyway, if we scroll down here to the answer, we will get to what Paul said. And of course, this is exactly the behavior of an appropriately configured API cursor. Now, if you look at this code, it is some of the most outlandish stuff I have ever seen written. Right? We have some things declared and set using these horizontal lines, some sort of XOR, bitwise, something or other to make numbers out of multiple numbers that make sense to API cursors.

And then there are some stored procedures like SP cursor open, SP cursor option and SP cursor fetch. And I believe it’s not this one. There’s also an SP cursor close. So this is where I first saw anything about API cursors. Now, if you work with SQL Server. And you work with like a vendor product and you see lots of queries running like fetch API something, something, something, something. They are using API cursors, but they are probably not using correctly configured API cursors. They are probably using just whatever stock crap they came up with. As we’ll see in a moment, the Microsoft documentation on API cursors is not good.

This is the other answer I saw where Paul brought up API cursors. I don’t think there are any others on Stack Exchange. There are probably some elsewhere in the world. But a favorite. Look at that. Look at that delicate U in there. Favorite solution of mine is to use an API cursor. I didn’t mention anything about it being correctly configured in this case, but we can assume because it’s Paul, it is correctly configured.

But it uses sort of the same set of stored procedures there. And there is this absolutely wild thing. Well, cursor status global my cursor name equals one. Keep going and finding things. So if you want some background and you want to see some API cursor code, the links to these will be in the video description.

Now, the Microsoft documentation on API cursors is incredibly sparse. Like you’ll get some like you’ll get like valid stuff in here, but there’s really no mention of like correctly configuring them. Like you get a lot of information about cursor stuff, which like if you understand cursors generally would make makes more sense to you.

But if you don’t understand cursors generally and you are the type of person who just, again, has the dog whistle response to like, oh, cursors. Oh, there’s very little hope for you anyway. There’s SP cursor. So we just looked at cursor open.

This is SP cursor, which has sort of the same set of stuff in there. And then we have SP cursor fetch, which does a whole bunch of other stuff. There are other SP cursor procedures in the mix, of course.

There are all sorts of weird things you can do with cursors that you probably didn’t know you could do. So there’s a wide world out there. Now, what I wanted to show you is the thing that I wanted to do.

Now, this is in no way supposed to besmirch the wonderful Michael J. Swart post about batching modifications. This is just an alternate approach to batching modifications. Not to say that you need to do this when you batch modifications, but you might be able to have some fun with it at some point in your life.

So I’m going to walk through the code and then I’m going to run the code. And I’m going to point out exactly where I got a little assistance with the code because things were kind of annoying. So I am creating a table of sample data.

That much should be very obvious. I’m going to put 10,000 rows into the sample data. And in those 10,000 rows, I’m going to mark 5,000 of them as needing an update. Right.

So case when this number, the module is 2 equals 0, then 1. So out of the 10,000 rows, like 5,000 of them will have the needs update set to 1 based on this row number. All right.

After that finishes, I’m going to show you the 5,000 rows that need an update. And then we are going to get into the cursor stuff. Now, the goal of this cursor is to update 1,000 rows at a time.

Right. And we’re going to handle that with some of these fancy parameters in here. Now, this is, again, not for the faint of heart.

This is a very difficult to follow set of things. There are a lot of things to declare and keep track of. But here is our query that runs when the cursor runs.

We are also going to declare this dummy table. And the purpose of this dummy table is to eat results. So usually on every time, on every execution of these cursor procedures, SQL Server returns a result set from the cursor.

I don’t want to see that because I want, like, rather, I didn’t want to see that because I was like, you know, like, they just clog up the screen. It looks silly. It makes me, you know, gives me the face vibrations I don’t like.

And so Paul suggested doing the insert top zero into dummy when executing the procedure. All right. So if that looks just stunningly out of this world, insane to you, that’s what that’s doing.

That’s the purpose of that. On each trip through the cursor, I’m going to select a row count so you can see that a thousand rows come out of a thing. I’m going to put this on GitHub.

I usually don’t, but this is so weird that, you know, screw it. I’m going to put it out there. And so this is what does the update in here. And this is so bizarre.

This is so weird. All right. I have to tell it which table to update, which I guess technically I can put an empty string in here because there’s only one table, but whatever. And then here’s what I’m doing.

I’m setting the price times 1.10. I’m setting last updated to sysdate time. And I am setting needs update to zero. Okay.

So then I’m going to show you via the row count big function that how many rows I do at a time. We’re going to fetch the next batch and we’re going to do all this stuff. And then at the very end, I am going to show you, I’m going to verify that the updates ran.

So if you are all ready to see this happen, let’s run this. And let’s admire these results for a moment. So these are the 5,000 rows that need an update.

All right. You’ll see that this, like just looking at the top, like I guess there’s eight visible rows here. We have two, four, six, eight, 10, 12, 14, 16.

We’re pretty much counting by twos. Right. These are all the ones that needed an update. Here’s the original price. The original quantity. I didn’t change quantity, of course, just price.

And then down here, you’ll see that these numbers did go up or rather these numbers did change. Right. So this is the last query that shows the data that I just messed with. Product two, four, six, eight, 10, 12, 16.

The prices are all 1.10 higher. And the last updated has been incremented to today. And the needs update is now set to zero. So these went from 2024, 630 to 2024, 317.

So this did work. And if you look at the five calls to row count in here, there’s 1,000, 2,000, 3,000, 4,000, 5,000. So I updated these 5,000 rows, 1,000 rows at a time.

And I did that using an API cursor. This type of syntax is not available. Rather, this type of behavior is not available using a stock and standard cursor.

You do have to get into the weird world of API cursors and do stuff like this. So when do you use these? These?

Probably when you have gotten to the point where you cannot be satisfied by normal queries. I get a thick callus for stuff in SQL Server these days. And this was a nice way to sand that callus down and feel things again.

So, you know, of course, thank you to Paul for the assistance with some of the coding here. And, of course, for publishing the original answers that opened up my world and mind to API cursors. And, yeah, I hope that this will encourage you to keep learning about things in SQL Server because there are all sorts of interesting things you can do once you peel the world back a little bit.

Anyway, thank you for watching. I hope you learned something. I mean, I’m pretty sure you learned something. I hope you enjoyed yourselves.

And, again, the links to the code and to the original DBA stack exchange questions so you can read more about API cursor stuff will be in the video description. But, again, this is a weird one. I admit it.

I fully and totally admit this was a weird video. But it was something that I was rather proud to show off because most of the stuff that I do is pretty much like, you’re going to see this every day and it’s going to be a problem.

This is nice and weird. Anyway, I’m out of here. I’m going to go un-weird myself a little. Goodbye.

Going Further

SQL Server Performance Office Hours Episode 7

Posted on April 7, 2025January 22, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 7

How do I size SQL Server for new project? Where to start, what to take into account? THX!

When are you going to start adding your referenced links to the details section so i can copy and paste? <3

Should one still care about Index Fragmentation in the days of Azure VM Premium disks, or local SSD’s

When (if ever) will we see a Tyson vs Paul type grudge match between Erik and Brent

your face looks smaller lately. r u sick?

To ask your questions, head over here.

Video Summary

In this video, I address several questions from viewers. First, I tackle the topic of index fragmentation in modern storage solutions like Azure VM premium disks and local SSDs, explaining that while logical fragmentation is less of a concern, physical fragmentation can still impact performance but isn’t typically measured by standard scripts. Additionally, I discuss my recent weight loss journey, attributing it to a combination of reduced gym attendance due to the pandemic and aging, which has made maintaining high strength levels more challenging. The video also includes some light-hearted questions about potential future collaborations with other industry experts and a bit of self-deprecating humor regarding my appearance.

Full Transcript

Erik Darling here with Darling Data and boy howdy we’re gonna do it. We’re gonna drop in office hours here. Where I answer five of your questions at a time that you submit to me through my form. If you want to ask a question here you can do that at this link. This link is available in the video description down below. While you’re looking at the video description down below, if you think, boy, this Erik Darling sure does produce a lot of content that I rather enjoy. Maybe I don’t always learn something. Maybe I do. Maybe I don’t always enjoy myself. Maybe I do. But I would like to support those efforts. You can, you can, while you’re, while you’re floating around down there, there’s, you can, you can become a subscribing member of the channel. And for as little as, as little as $4 a month, you can, I don’t know. What, what is, what is $4 a month? At the end of the day. It’s really what it, what it does in the aggregate, right? About 60 other people have decided to be so kind as to support my efforts here. So, uh, you would, you would join the, that choir of angels.

And I would, I would be eternally grateful to you in the aggregate. Uh, if you, if you ran out of $4 a month, and maybe you died, uh, you can, you can, from the grave, you know, this is America. So, if you can, dead people vote. So, if you, dead people want to like, comment, and subscribe, you can, uh, you can, of course, do that as an alternate means of supporting the channel, uh, from this mortal coil or, uh, from, from, from beyond. If, uh, dead or alive, you would like help with your SQL Server, uh, I am, I am very good at all of these things. Uh, some, some, by some, I mean the, the nice folks at Beargut Magazine might say that I am, I am the best in three out of four hemispheres of the world at it.

Uh, if there weren’t for that damn island nation in the, in the Pacific, I would, I would reign supreme over the entire globe. And if you need a health check, some performance analysis, some hands-on tuning, uh, if you are having a performance emergency, or if you want to get your developers training so that, uh, your SQL Server stops being consistently on fire, well, you can call me up and I’ll do that. And as always, my rates are reasonable.

Uh, if you would like some reasonably priced SQL Server performance tuning training, I have that as well. About 24 hours of it. Uh, available at that URL with that, with that coupon code, which is also fully assembled for you down in the video description.

So, there’s a lot, there’s a lot you can do with the video description that will, that will clarify many things in your life for you. Uh, SQL Saturday, New York City. Come in your, come in your handy dandy way, your happy way, uh, on May the 10th.

That is this May the 10th of 2025, taking place at the Microsoft offices in Times Square. Highly suggest you, uh, find that event and buy a ticket because, uh, space is limited and those tickets are going, what, faster than Sbarro pizza slices. So, that out of the way, uh, let’s, let’s do this office hours thing.

Got, got quite a lineup of questions here. Uh, first and foremost, uh, we have this question. How do I size SQL Server for new project?

Where to start? What to take into account? Fix. Well, uh, there’s a lot of stuff to collect on this, right? Um, new project.

Wow. A lot of stakeholders involved. Um, uh, you know, some, something that might help is if, uh, this is, uh, a new project. That, uh, is using an existing third-party piece of software.

Third parties will often publish some sort of minimum specs for, uh, what, what they expect out of a SQL Server. Uh, this would include hardware, version edition, all that good stuff. Um, and, uh, you, if, if this is a, for a third-party piece of software, you might, you might even ask them, uh, what the typical, what the average installation is.

Um, is for a SQL Server, at least, at least as far as database size goes. They might, they might have some ideas there that would help you out. Um, but if I, uh, you know, other things that you might want to take into account is talking to various stakeholders about the importance of this project.

Um, you know, it might, it might be something where, uh, you know, high availability and disaster recovery are a must off the bat. It might also be a thing where they’re like, let’s just, let’s just build an MVP and see how it goes. Uh, a lot of the times, um, you know, when these projects start off, uh, they are on, uh, you know, the, the database has no data in it.

So, if you’ve got that going for you, it doesn’t, almost doesn’t matter what, what size SQL Server you start with, uh, especially given the flexibility of hardware configurations, both with virtual machines and the cloud these days. Um, uh, I had another thing to say there. Um, if this is, uh, you know, a new project that is perhaps based off an existing project, you might take a look at the, the current set of things there.

Uh, like, you know, whatever, whatever hardware is in the current SQL Server and all that. Um, look, there’s, there’s not, there’s not a whole lot here for me to go on. So I could just keep listing off different things that you, you might think about and ask as you’re doing this.

But, uh, really, um, this is the sort of thing where that, you know, DBAs do, do have to get paid for because sizing a SQL Server is not just a one-time set it and forget it thing. You need to, you need to keep an eye on the performance of this SQL Server if this is something that you care enough about to, to ask the question. Uh, I would say that, uh, you know, you want to keep an eye on those weight stats and make sure that your hardware is keeping, whatever hardware you initially assigned to, that is keeping up with the workload, the number of users, all the queries that are in there.

Um, you know, if it’s a, if it’s an in-house project and people keep adding features and stuff, you’re going to have to keep looking at indexes and other aspects of the database to make sure that it stays in touch with development reality. So, uh, I don’t know, like, I think maybe, maybe it depends a little bit on, uh, how much people are willing to spend on it at first. Uh, you know, if it’s, if it’s going into, it’s going into the cloud, uh, whether it’s on a VM or on a platform as a service offering from any cloud vendor, you might want to ask what people are willing to spend on it because that’ll do a pretty good job of dictating, uh, exactly how much hardware you can get out of it.

Uh, if it’s, if it’s going to be an on-prem virtual machine or something, uh, then it has a little bit less effect. But, uh, you know, you might start, you might think about like, um, you know, for standard edition, right? And I’m not, I’m not saying that you would ever use standard edition.

I’m not calling you that much of a cheapskate. But what I am saying is that for a lot of people, when they build a standard edition box, uh, like VM somewhere, uh, they will, uh, choose a set of hardware. Uh, that maximizes the capability of SQL Server standard edition, uh, you know, somewhere between eight and 24 cores, depending on, uh, the workload that hits it.

Uh, 192 gigs of memory because SQL Server standard edition, uh, well, it is capped at 24 CPUs these days. And at least as far as I’m aware, SQL Server 2025 has not changed any of the capacity limits for SQL Server standard edition. So you still have the 128 gig cap on the buffer pool, but what you, but, uh, what, what a very common practice with standard edition is, is to give the SQL Server, uh, about 192 or so gigs of memory.

Set max server memory somewhere in the 180s. And that way you have 128 gigs of data, of memory rather for the buffer pool. And then SQL Server is allowed to use memory between the end of the buffer pool and max server memory for all sorts of other things.

So it might be smart enough to just start all your builds with a maxed out standard edition build, even if you’re using enterprise edition. That’s, um, usually an okay way to go. Of course, uh, CPU count has a much bigger impact with enterprise edition than with standard edition.

Um, it being the, the, the $2,000 of a core versus $7,000 of course. So again, it really does come back to budgetary constraints and what people are willing to spend on this hardware. Doesn’t it?

So make sure you get those numbers, right? Second question here is, when are you going to start adding, oh, that was the wrong button. When are you, when is zoom it going to start listening to me?

Uh, when are you going to start adding your reference links to the detail section so I can copy and paste? Well, uh, I, I, I always endeavor to include all of the, the reference links in my, in my video descriptions. If I ever miss them, please feel free to point it out.

Uh, and I will correct, I will aim to be as eventually correct as MongoDB and, and, and, and get that in there for you. But, um, I am, I am an imperfect soul and all I can do is beg for your forgiveness and, and, and, and try to correct any, any issues in that area. And let’s see here.

Should one still care about index fragmentation in the days of Azure VM premium disks or local SSD, SSDs? What? Apostrophe abuse right off the bat.

Uh, no. So look, this is something that I’ve, I’ve, I’ve talked about a bit. Uh, the type of index fragmentation that most people, uh, cared about at some point in time was logical fragmentation. It’s data pages being out of order, uh, on, on, on disk.

And that is, no, that is not something that I tend to care much about when it comes to, um, when it comes to SSDs or flash or, or, or memory or like, you know, RAM memory, uh, not RAM disks. That’s not for SQL Server. Uh, but, uh, there, there is always a specter of physical fragmentation that is empty space on data pages.

Um, and that can affect scan density. That can affect read ahead, read size. And, you know, it might be something that you want to look at.

The problem is that, um, not, not many, uh, readily available, uh, index maintenance scripts measure physical fragmentation. They all measure logical fragmentation. And there’s not really a good correlation between a logically fragmented index and a physically fragmented index in either direction.

Uh, if you want to go and start measuring, uh, uh, physical fragmentation. And you want to start, uh, rebuilding or reorganizing indexes or in order to, uh, cram your data pages more densely, uh, with data. Then you are, you are welcome to figure out at what threshold that makes sense for you to do and, uh, and pursue that endeavor.

But, um, if you are, if you are just asking me, like, how to configure all the scripts or something or something like that, it’s not in there. So, um, that is something that you have to kind of figure out a bit on your own. And it’s not something that, that I want to get into the business of doing because there are all sorts of situations out there where, uh, even physical fragmentation would have no profound effect on a workload.

If the queries are, if the majority of the queries are performing index, index seeks, it is index scans that are affected by the page density that is lessened by physical fragmentation. And seeks don’t really have that sort of performance hit. So, uh, this is a weird question.

When, if ever, will we see, no, zoom it, listen to me. When, if ever, will we see a Tyson versus Paul type grudge match between Eric and Brent? It’s a, it’s a very strange question.

Um, I wasn’t aware of a grudge between myself and Brent. Uh, if there is one perhaps that I’m unaware of, uh, you can feel free to, uh, enlighten me about that. Uh, at least the last, last time I spoke to him, uh, fairly recently, there was, there was still no, there was no grudge.

So, uh, the, the, the, the, um, potential for a grudge match does infer the existence of a grudge. But, um, at least I am unaware of one. And finally, your face looks smaller lately.

Are you sick? Well, thanks for noticing. I do appreciate it. Uh, a fella does work hard to, to keep in shape. Uh, no, I, I’m, I’m, I’m not sick.

I am, I am, I am as well as I’ve ever been. Uh, I did, I did lose some weight though. Uh, if you, if you want, if you, if you, if you care about a full, uh, the full story, uh, you can, you can keep listening. If you, if you don’t care for, uh, any sort of explanation, uh, you can, you can stop watching the video now.

But, uh, around about, uh, 2016, I got very much into, uh, strength training and like sort of a, like a powerlifting type thing. And, uh, you know, before that, I, I just kind of horsed around in the gym like many misled, uh, time-wasting young gentlemen. And, uh, I just realized, after a while, I was just like, this isn’t really getting me where I wanted.

So, um, I, I, I discovered the, uh, the starting strength program by, by Mark Ribiteau. And I started doing, uh, the novice linear progression. And, uh, after, after a few years, uh, I got, I got my lifts up pretty good.

But I, I was also, uh, you know, that, that getting stronger does require putting on body weight. Generally, if you want to gain muscle, you’re bound to gain some fat in there. But, uh, you know, I, I, I bulked up to about 225, 220 in there.

And, uh, you know, my lifts were my, but I had like lifts to match that. You know, I was dead lifting around 600. I was squatting over 500.

Um, I was benching over 300. I had a 250 something pound overhead press. And these are all for like singles. This wasn’t like me just banging out reps with that stuff. But I did get my lifts up pretty high.

And, uh, but, you know, I felt kind of okay about it because that’s the goal I was pursuing at the time. And the additional body mass was, uh, was sort of required for me to pursue that goal. But then, you know, um, COVID came around and, uh, in New York, gyms closed down for quite a while.

And then when they reopened, uh, you still had to wear a mask in the gym, which I, which I did try to do a couple of few times, but for the type of lifting that I was doing, it was very uncomfortable.

So, um, you know, I, I did, I did drop my weight back down a little bit and, uh, then I lost about 20, 25 pounds, uh, because I just wasn’t working out and, you know, carrying all that around when you’re not actively lifting heavy weights is kind of, but then I could just never, um, you know, uh, I got older, my, my, my, my mid middish forties.

And, uh, I could just never get, um, the type of training going where, uh, I was, I was getting my lifts back up to that. Right.

After take, after that long of a layoff in the gym, it’s like, you’re starting basically from scratch. And so, uh, yeah, you know, I just, I just kind of realized that, uh, is a, is a, is an aging gentleman and, uh, you know, uh, sort of struggling a bit with training consistency and stuff like that, that I just wasn’t going to get my, my lifts back up to where they were.

So, uh, I just, you know, decided, you know, lifts be damned. Unfortunately, uh, I, I, I did, uh, pursue just losing weight. I was still, still, still lifting weights, but all of my, the, like my, my lifts for what they were just dropped way down.

Uh, now, um, you know, just sort of, uh, slowly trying to add back some, some strength into the mix and, uh, get them at least back to some respectable numbers. But, you know, that’s going to be a slow process because, uh, I am not, uh, I am not in the same, uh, bulking frame of mind that I was the first time around.

So, you know, it’s, you get a bit stronger, but also not, you know, bulk up to some horrible weight. I am at the, I’m at the point of my life where doctors are starting to prescribe interventions for certain things like blood pressure, cholesterol, and all that other stuff they give you because you’re going to die if you don’t take it or something.

So, uh, uh, I’m not sick, but, um, I was, I was feeling not so great there for a bit. Uh, cause, you know, I had, I had all of the, uh, the, the outer symptoms of, uh, of, of a powerlifting career without any of the, the, the strength that went along with it, which is not a, not a terribly good combination.

Uh, so, that’s my story there. If you listened, thank you. If you didn’t, I totally understand. Anyway, uh, that does bring us to the end of this office hours. Uh, again, you can submit your questions, uh, via the, the link down in the video description that says office hours, and I will happily answer them.

So, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you, uh, if you are a young, young person wasting your time in the gym, you will stop doing that.

Get some, some barbells in your life. It’s a much better way to live. Anyway, thank you for watching. Goodbye.

Going Further

Why Does My Trigger Have Multiple Plans In SQL Server?

Posted on April 4, 2025January 22, 2026 by Erik Darling

Why Does My Trigger Have Multiple Plans In SQL Server?

Video Summary

In this video, I dive into an intriguing aspect of SQL Server: why triggers might have multiple execution plans. Erik Darling from Darling Data Enterprises shares his insights on how and why these plans can differ based on the number of rows being processed. He explains that SQL Server caches plans for single-row and multi-row scenarios separately, which can lead to confusion if not understood properly. Along with practical demonstrations using SSMS 21 preview in dark mode (your feedback on this setup is welcome), I walk you through how to identify these different execution plans within the plan cache. This knowledge is crucial for anyone dealing with complex trigger logic and performance tuning in SQL Server environments.

Full Transcript

Erik Darling here with Darling Data, and we have a very exciting video for you today here from Darling Data Enterprises. This is all about why do I have multiple plans for triggers? And there are, you know, probably some other external reasons why you might see multiple plans for the same trigger. For example, if you have the same trigger across multiple databases and you look at the plan cache and you don’t take the database context into account, you might see multiple things in there. But this is a much more interesting internal reason for why you might have multiple plans for your trigger. Before we get into all that, man, I love you all so much for the support that you give this channel. And if you want to be included in the the people who I love for giving support to the people who I love for giving support to this channel, you can do a couple things. You can sign up for a membership. And if you do that using the link down in the video description for as few as $4 a month, or we’ll call that one espresso buck, you can support the content that I create on this channel. If you have spent all your money on caffeinated beverages or other assorted methamphetamines or uppers I don’t know whatever whatever you’re into poppers, maybe you can like you can comment you can subscribe. And if you want to ask a question privately that I will answer publicly during my office hours videos, that link right there by my my very fancy extended pinky is down also in the video description.

Slide, please. If you need help with your SQL Server beyond the scope of what a simple question or a YouTube video or a blog post or anything else can help, and you’re in the market for a young, handsome consultant with reasonable rates, I am the best in the world at all of these things.

That’s a short list of all the things in the world I am the best at, but this covers most of the ground with SQL Server. There are several other things not SQL Server related in the world that I am best at, such as picking the bottle of wine from the wine list that the restaurant has run out of. I am tops at that. Cannot be, cannot be beat. I am undefeated, undefeated at that.

Indefeatable. Invictus or something. If you would like to get some training on SQL Server performance tuning and you don’t feel like spending hundreds of dollars or thousands of dollars a year for a subscription or whatever, you can get all of mine for about 150 bucks and you can get that for the rest of your life. There’s about 24 hours of it and the fully assembled method for retrieving this wonderful deal is also down in the video description yonder over there.

All right. SQL Saturday, New York City 2025 on MAY. That is May the 10th at the Microsoft offices in Times Square. I will be there in various capacities doing things. I don’t know. I’ll probably even be wearing the same outfit, so I will be highly recognizable to you, the general public. But with that out of the way, slide please. Let’s talk about why triggers might have multiple plans. Now, I need some helper objects like some tables and just to make life easy, I’m just going to have a, you know, just a couple rows, trigger test, trigger audit. We’re just going to pretend this is an audit table that captures stuff about what got put into the test table. Then we’re going to have a trigger and it’s going to be an after insert trigger like so. All right. And we are going to just insert whatever stuff from the inserted virtual table exists into the audit table, right? So very simple thing there. Nothing, I hope, too out of the ordinary. We should make sure that we do this correctly, though. We should do create or alter. And I am recording another video here using the SSMS 21 preview with dark mode in there. If there’s any feedback on my use of dark mode or my use of SSMS 21, please let me know because I want to make sure that I’m making the best possible videos. I know some folks out in the world dislike dark mode. Other people love it. So I don’t know. Just kind of tell me how you’re feeling about it. That would be wonderful. So to round out this demo here, we’re going to clear out the procedure cache. And I’m going to pause now to tell you that SQL Server caches plans for triggers in two different ways internally, like a plan caching mechanism.

There is a trigger, a plan for your triggers that will be for a single row. And then there will be a plan for your triggers when there are multiple rows in the inserted or deleted virtual table. So that’s what we’re going to look at here. And that’s what I’m going to show you with my fancy query down below. Now, right now, of course, there should be nothing in the plan cache since I just cleared it. And we’ll tell us about that. And in between recordings, I managed to remember to increase the size of my grid text. So now we don’t have to go blind together staring at that.

But what we’re going to do now is insert a single row into our table. And now let’s interrogate the plan cache. And we will see that we have a plan cached for a single row in there, right? Which is exactly what we did. Now, if we insert multiple rows into our trigger test, we are going to have a second execution plan added that is a multi row. So here we go. We have the one use count for our trigger object type. And the set options, if you do some fancy, I forget what this is called bitwise, something maybe I forget. But if you do this, and 24 for the set options attribute, and DM exec plan attributes, you can decode between multi one row and multi row trigger plans. So if we look at the first plan that we cached, and we look at the inserted plan, we will see that the number of rows in that is all estimated at one. And if we own what we should probably close that out. See, this is the one thing that I dislike about the dark mode is like not everything is dark mode yet. So when you get when you do things like open up query plans, it’s like, you can like go blind. It’s like, like in Big Trouble in Little China, when David Lopin does the eye light thing at Kurt Russell. Jack, whatever his name is in that. And I don’t know if I clicked on the right one there. Let’s go back and try that. Let’s make sure I did. All right. So if we now click on the execution plan for the multi row trigger, and we look at this, we will see that these have changed, right? Or rather, these are just different in this in this plan. These numbers have changed between the single row plan and the multi row plan. Obviously, now we have three rows instead of one. So if you are looking at your plan cache, and you are puzzling as to why you have multiple plans for some of your triggers in there, the answer could be as simple as you are storing a plan for the execution of the trigger for a single row. And you are also storing a plan for the execution of the trigger when it processes multiple rows. Perhaps not the most interesting, perhaps not the most titillating, psychologically traumatizing SQL Server content that I’ve ever produced, but it is a useful bit of SQL Server knowledge and trivia nonetheless.

So yeah, I’m probably just gonna can this one here. We’re gonna talk about some other stuff coming up in other videos. We’ll probably continue on with the store procedure series because I owe you a few things. I owe you a few videos remaining on that. I believe we have four or five left to cover. So we’ll get those done. And I don’t know, see, we’ll just see what happens next. The world is our SQL oyster, or something like that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video. Goodbye.

Going Further

All About SQL Server Stored Procedures: Dynamic SQL For Performance

Posted on April 3, 2025January 22, 2026 by Erik Darling

All About SQL Server Stored Procedures: Dynamic SQL For Performance

Video Summary

In this video, I delve into using dynamic SQL to address performance issues in stored procedures, particularly focusing on the pitfalls of parameter sniffing and if-branch statements. I demonstrate how dynamic SQL can help mitigate bad estimates and unwanted query plan compilations by ensuring that each branch is treated uniquely. By incorporating a “replace me” token within the dynamic SQL queries, we inject specific conditions based on input parameters, forcing SQL Server to generate distinct execution plans for different scenarios. This approach not only tackles cardinality estimation issues but also helps in managing parameter sensitivity across executions. I walk through creating and executing stored procedures that dynamically adjust their query text based on input parameters, showcasing how this technique can significantly improve performance by avoiding the pitfalls of traditional if-branch logic.

Full Transcript

Erik Darling here with Darling Data. And in this episode, where we will continue to sermonize about stored procedures, we’re going to talk a bit about, of course, using dynamic SQL to fix performance problems. There are three main problems that we’re going to talk about, and then one fourth sort of bonus problem. So we got that to look forward to over the next episode. So we’re going to talk about the next, the rest of our lives. But before we go into all that, of course, let’s talk a little bit about some fun stuff, some interesting things in our lives. If you like my content, or, I don’t know, if you find the things that you watch and learn here worth money, you can click the link down in the video description, right about there, and you can you can become a subscribing member of the channel for as few as $4 a month. If you are unable to scrounge $4 a month from the couch cushions, or mom’s purse, or whatever, you can do other stuff to support my efforts here. You can like, you can comment, you can subscribe, and if you would like to ask a question privately that I will answer publicly during my Office Hours episodes, that link is also down below for you to do that.

If you would like me to show up semi-live, probably via Zoom call, but if you want me to show up to your offices, it’s fine with me. I’m not going to complain too much. Money’s money. But I can do all of these things here, and I do them better than anyone else in the world outside of New Zealand. Health checks, health checks, performance analysis, hands-on tuning, responding to performance emergencies, and training your developers so I don’t have to respond to performance emergencies. All worthy goals, and as always, my rates are reasonable. If you would like to get some training for yourself, or maybe a friend, family, colleague, I don’t know, whatever it is, you can get all 24 or so hours of mine for about $150 USD using that discount code at that URL up there.

That is also completely assembled for you down in the video description. SQL Saturday, New York City 2025 is coming your way May the 10th. I will be there slinging sandwiches and cookies and bags of chips right in your face.

We have a performance tuning pre-con on May the 9th with Andreas Walter teaching us about performance doodads and gizmos and whatnot. So I will also be proctoring that. So at the very worst, I can throw a sandwich in your face two days in a row.

With that out of the way, though, let’s talk about dynamic SQL stuff. And I have already done the wrong thing. So I promised a while back that when SSMS 21 had support in SQL prompt that I would do one of these using SQL prompt and the dark mode thing.

So now SQL prompt 10.16 added support for SSMS 21 preview. Refer documentation. So there are a few things that I had to do to get SQL prompt showing up in here.

But that’s okay. So this is SSMS 21 with dark mode. The things are all dark.

Some of the things are all dark. Some of the things are not dark yet. Namely query plans. Query plans are very much not dark. But, you know, there’s only so much you can do. I’m a little blurry here.

I think I want to crisp myself up a little bit. There we go. There we go. Now I’m feeling crispy. Maybe? No, I think I went a little too uncrisp. No, that’s less good.

There we go. All right. Now I’m feeling zombified. All right. So the stuff we’re going to talk about. Dynamic SQL. Very good for things like if branch, plan compilation, parameter sensitivity, and complex runtime logic. Things that you put in your join or where clause where it’s like where parameter equals something.

Then do this other thing. And if the parameters are variable is this other thing. Do this other thing.

And add this other thing on. Explore this branch. Because all that stuff sucks for the optimizer. And we’ll talk about that. We are also going to talk about one bonus topic around filtered indexes in Dynamic SQL. And we will use a somewhat similar pattern to get around some limitations there.

But my goal here is to show you an example of, well, I guess, all, not both, of all of these things. And build on some of the Dynamic SQL stuff that we talked about in the previous video. About using Dynamic SQL safely and correctly.

For a lot of the things that we are going to talk about with Dynamic SQL. I’m going to be just upfront and honest with you. A statement level recompile hint would solve a lot of these problems.

Not a store procedure level recompile hint. But a statement level recompile hint would solve the majority of this stuff. And you would not have to write or worry about Dynamic SQL.

Whether that’s appropriate for whatever situation you are in is up to you. If you want to use a recompile hint, I don’t care. It doesn’t bother me.

I’m not here to, like, warn you of some atrocity. Your CPU is catching fire or anything like that. I use recompile hints all the time. They’re fantastic. They solve a lot of stuff. You just can’t always get away with it.

There are also situations that we are going to talk about. Where nested store procedures. The wrapper store procedures. Like we talked about in another video. Would be either sufficient or preferred.

Usually things around security and permissions. Would drive you to that over Dynamic SQL. I’m not saying that Dynamic SQL can’t be done correctly to do that stuff.

I’m just not the person to get training from about security and permissions. Because I don’t give two toots of a horn. About either one. But once you get into using Dynamic SQL.

There are all sorts of fun and creative ways to use Dynamic SQL. To sort of avoid lots of problems in here. So let’s dive right into it here.

So I’ve created a couple indexes. On the post table. Well sorry.

One on the post table. One on the votes table. And we are going to be using those in our first store procedure example. Now we’ve talked about this in the past. But this is the store procedure series. So we’re going to talk about it again.

Again we’ve got a query here that will run if post type ID is not null. And we’ve got a query here that will run if vote type ID is not null. But as we have talked about in previous iterations of discussing this sort of if logic and store procedures.

SQL Server does not do. Oh I should have a go in there. Just safe.

It’s to be safe. SQL Server does not do a particularly good job of managing plans like this. If I go and I grab the estimated plans for these two things. We’re going to see some stuff that looks rather different. Take a look at this one.

Where the top branch is parallel. And the bottom branch is serial. Single threaded. And now we look at the bottom. The second execution. Where the null and not null parameters have been reversed.

And the top branch is single threaded. And the bottom branch is parallel. What you’re going to notice about both of these. Is that the non-parallel branch only has a one row estimate.

And that’s going to be true for up here as well. Now since I have just gotten estimated plans for these. We have no cache plan.

Which means whatever order I run these in. And compile a plan for. And we cache a plan for. Will be the one that we get the better estimate for. For the non-null parameter value.

So if we execute this. Where post type ID equals four. We get a perfectly fine execution plan. That is the parallel plan that we discussed above. SQL Server is asking for an index on the post table.

But as of right now. The way that we’re hitting the post table. Is not of any significance. This of course goes right down El Tubo. When we run this for vote type ID 10.

Because now post type ID is null. And vote type ID is 12. And this takes a full eight seconds. To give us a query plan.

And we can see. Where that one row estimate. Is no longer our friend. Because we got a whole bunch of rows back. And we spent a whole bunch of time. Doing all this stuff.

If we look in the properties here. And we look at the vote type ID parameter. You’ll see that it was compiled with a null. But executed with a 10. So we are already off to a very bad start. Now of course.

If I flip this around. And I run it for vote type ID. Let’s just say 12 first. So we get a pretty quick execution here. Then what we’re going to end up with.

Is a serial plan here. It has correct estimates now. For that vote type ID. But now when we go and run this. For post type ID equals one. The post type ID plan.

Is going to be the one row estimate. And if you’re familiar with. You know. Either my videos. Or the Stack Overflow 2013 database. You will know that post type ID one.

Has six million rows in the post table. Not one row. So this takes. 16 seconds to run. And you can see where this was. No longer such a great idea. Estimating one row here.

It’ll be the exact same scenario as above. Where post type ID. Would have been compiled. With a null value. From here. And we would not have a good time.

Now this does make an assumption. That either one or the other. Will execute. But this is the way. I see a lot of store procedures. Set up to run. So don’t tell me. That this is unrealistic.

Because a lot of the stuff. That I end up tuning. Looks a lot like this. So. I’m just going to have to deal with that. What we can do. To prevent. The execution.

Of unwanted. Or rather the optimization. And compilation of query plans. For unwanted. Or unexplored if branch statements. Is to make the whole thing dynamic.

What this will fix. Is the bad estimates. That come with. The queries that are in the if branches. This will not fix parameter sensitivity.

Within parameter uses across executions. One thing that it is very important. And important to note. Is that in. Like it doesn’t matter. With the if branch.

And it doesn’t matter. With using dynamic SQL. In this way. Like you still are. You still have the potential. For parameter sniffing. So.

Now. Another thing to keep in mind. Is that we can no longer. Just recompile the store procedure. In order to show plan differences. Now we have to. We do have to clear out the procedure cache. Because.

Or like. We could clearly look up. Like SQL handles. Or plan handles. Or something. To do this a little bit more surgically. But. Just a quick means to an end. For these demos. Is to just run dbcc free proc hash. To clear stuff out.

But just to show you what I mean. About the parameter sensitivity thing. Like if we. Hit control and l. To get an estimated plan here. Notice that we no longer have.

Any of this stuff. Like we no longer have. Like the full query plan. For either of these things. Coming out here. Right. We just have execute proc. Which means that. The.

Like when we run this. That query responsible for vote type id. Won’t do anything. Right. It’ll just be a normal. Normal. Like. It’ll just get passed over. Right. It’s left alone.

It’s only if that. If only if we. When we hit something. That gets executed in here. That a query plan. Arises for this. But this is what I mean. By the parameter sensitivity thing. Even using dynamic SQL.

If we run this for. What was that? Post type id 4 first. And then post type id 1 second. Post type id 1. Reusing the query plan.

For post type id 4. Does not work out so well. Right. This is not a good time. This takes six. Six seconds to run. We end up spilling a whole bunch of stuff.

Here. And here. And it. Like really. Like. We just. Like. We solved. Like one of the performance problems. But we still have. An additional performance problem.

And it doesn’t really matter. Doesn’t really matter much. Well I mean. It does matter that we fix the. First performance problem. With dynamic SQL. But. We still have the parameter sensitivity issue. To deal with.

We can fix that. By looking at. Some. Sort of like the frequencies. That these. Post and vote type id. These occur.

In the tables. And I still have to fix. The font size on this. But for now. We can just use some advanced. Zooming. Unadvanced zoom hitting. Apparently on that.

And we can look at the counts. For these things. And we can figure out. Like maybe. We can sort of do. Our own version. Of the parameter sensitive. Plan optimization. And bucket these things in.

In. Ways that make sense. Right. So what we’re going to do. Is we’re going to create. This procedure. Called if branch. Compilation dynamic plus.

And this is going to take. An extra step. Along the way. What I’ve done. Is I’ve bucketed. What. Well actually. Start up here a little bit. In both of the.

Dynamic SQL queries. We now have this little token. That says replace me. Right. And down. Before we execute this. We’re going to take one more step. With the dynamic SQL.

SQL. And we are going to say. Replace. And. We’re going to look in the. SQL. Placeholder that we have here. For the text. At replace me at. And if post type ID equals one.

We’re going to inject. One equals select one. If post type ID equals two. We’re going to inject. Two equals select two. If post type ID is in four or five. Then we’ll do three equals select three. If post type ID is in three.

Six seven eight. Then we’ll do four equals select four. And if someone passes in. A completely different post type ID. We’ll do five equals select five. What putting this branch in. Or what putting.

Doing that replace me thing does. Is it prevents. It like basically. Like makes the query hash out. To a different value. And it makes SQL Server. Come up with a unique query plan.

For any one of those. Select one equals. Whatever. Two equals. Three equals. Four or five equals. So we’ll get a different query plan. For each one of those. I’ve also done something similar.

With the vote type ID branch. We have the same replace me thing here. And we have a very similar. Replace call. With different vote type ID. Things.

Now remember. For vote type ID equals two. Do I still have those up? No. I got rid of those. For vote type ID equals two. That was an island unto itself. With 37 million rows. So we want that thing.

To be isolated. All on its own. But if we run this now. For post type ID four. Like we did last time. We still get a nice. Quick execution plan. For vote type ID four.

And you’ll see the. And three equals. Select three. Injected into the query there. If we run this. For post type ID equals one. We will.

I mean. Granted. There’s there’s stuff. We could do. Probably to tune these further. I’m not saying that. Like these couldn’t be better. But this does solve. The majority of the issues. That we first saw. With just the normal. If branches.

And like the. The plan compilation. Cardinality estimation thing. And then later. The parameter sensitivity thing. With sharing plans. Across different parameter values. For both post type ID.

And vote type ID. So now. In this one. We have one equals select one. And these two things. Definitely got different. Execution plans. And the same thing. Will work for vote type ID.

If we run this for vote type ID 12. We get this silly little execution plan. We’ll see three equals select three. Injected into the executed query there. And if we run this again for 10.

We will get a completely different execution plan for that. And we will see the one equals select one. Injected into the query text there. So that solves the problem for us.

With both the if branch compilation. Cardinality estimation problems. And then later.

The parameter sensitivity issues. Now next we’re going to talk about. Replacing complex query logic. With dynamic SQL.

This is a very simple demo. You know. Just make sure I can get the point across. And sort of a reasonable time frame. We’re going to have this procedure here. Called complicated runtime logic.

And we have two parameters here. One called check posts. And one called check comments. And what that ends up as. Is an exist check.

If check post equals true. And then another exists check. If check comments equals true. There are all sorts of ways you could arrange this. That won’t make a lick of difference.

You could use case expressions. You could use like. Like an and outside of the exist. You could write this in any number of ways. But as long as you write this in a way. Where SQL Server has to do this thing.

No matter what. You’re going to get weird execution plans from that. So let’s just make sure we have this created correctly. And now let’s do a worst case scenario.

Where we execute this for first. Both things being false. And we get this query plan. Right. Maybe not the best query plan in the world.

But you know. This is what happens. And now if we execute this for both of these being true. This is going to take a little while to run.

Because SQL Server did its cardinality estimation. With those branches not having anything going for them. Now. SQL Server is actually executing the query.

And having to deal with the repercussions. Of such terrible cardinality estimates. If we look at the execution plan for this. This is what it looks like.

We have. 17 million of one. And a lot of one. And a lot of 10. And what happens is.

When you write it like this. SQL Server uses what’s called. A startup expression predicate. And these get sniffed. Just in the same way. That any other parameter can. So if check post equals one or true.

Then this will do something. But it did the cardinality estimate. For check post. For check post being zero. Or false. So we got just a real crappy plan. That took almost 20 full seconds to run here.

This is another just like. We could spend all day looking at. Well not all day. We could probably spend like another couple minutes. Looking at like true false false true for this.

But this is good enough as is. What I want to do here. Is just use a dynamic version of this. To show you how this would work. In real life.

Where. With dynamic SQL. Where we would just simply do this. And just for convenience. Where one equals one here is fine. And then if check post equals true.

Then we’ll tack this exist clause on. And if check comments equals true. Then we’ll tack this thing on in here. And that should be all fairly straightforward. But if we run this for.

Check post equals false and whatever. Then we just get a count from the users table. Which I probably messed something up logically. In the first one here. But you know.

We got zero back from that. Not a big deal though. It’s good enough to get the point across. But now if we run this for check post equals true. And check comments equals true. We get a much different execution plan.

Where when SQL Server actually had. To append these checks in. Then it used them. So we use the indexes that we created. And we scan them.

Which is fine. Because we’re doing hash joins. And we don’t really have much of a where clause on there. But this is another way to solve. A complex query logic problem. So the last thing that I want to show you. Is how you can use sort of a similar thing.

To deal with filtered indexes. With dynamic SQL. So normally when you create a filter. When you create a filtered index.

Right. Which we’ve done here. Where reputation is greater than 100,000. And you run a parameterized query. And even if that parameterized query matches that expression. SQL Server can’t use that index.

Because SQL Server needs to cache an execution plan. That is safe for any parameter that gets passed in. That’s what you see here.

So SQL Server will warn you about this in the query plan too. If you look here. We’ll see this unmatched index thing. It will tell you that we had an unmatched index.

Because of parameterization. That is the index that we created on the users table. And we have this unmatched index warning down here as well. So all this stuff will tell you. That there was a filtered index available to use.

But SQL Server was unable to use it. And of course using an approach just like before. Or we can do this. Right.

And what we’re doing here is just saying. If reputation is greater than or equal to 100,000. Then replace greater than or equal to reputation. With greater than or equal to 100,000.

Like I know. Like this doesn’t actually like help a lot. Right. Because this is just saying. Like if we have like if like reputation.

We passed in reputation. It’s like 100,001. This wouldn’t make any sense. Right. So like just to help you get around this. Just to give you an idea of a way to get around this. This is what you would do.

Right. So what you would. So kind of like the idea here. Is to just give you. Like show you an example of one thing that you could do. Where this would work out. And if we run this.

Now all of a sudden. Our execution plan will show a scan of our nonclustered index. And we no longer have the unmatched index warning. And rather than having a parameter in here.

We just have this in here. Now there are different ways to accomplish this. You could. You know. Instead of using replace with literal values. You could.

You know. Concatenate whatever the reputation parameter is directly into the string. You could also like insert the reputation parameter into. Like a table variable or a temp table.

And do the replace based on like whatever value that is. There are other ways you could do this. That would. That would. That would have. That would work just fine. For like any value that got passed in here.

And also. You know. Just to complete the circle. An option recompile hint. Would also allow you to bypass this. Because you would. Like you would.

Like you would get the parameter embedding optimization. With a recompile hint. That you wouldn’t get otherwise. So another approach to this. Might be to say something like. If reputation is greater than or equal to 100,000. Then add option recompile.

Onto the end of this string. Right. So there are various ways to take care of it. This is just a simple one. To help you sort of understand the problem. And different ways to approach it. So these are typical ways that I use.

Dynamic SQL to fix performance issues. And SQL Server store procedures. Again. Statement level option recompile hints. Do fix a lot of this stuff.

For free. Without. Or not. Not exactly for free. But without having to write a whole bunch of dynamic SQL. And worry about stuff. The option recompile hint. Does have compilation overhead. So if you have.

These queries take a long time to compile. It might not be the best idea. But. You know. I think that’s a fairly rare problem. Anyway. Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you. In the next video. Where we will talk more about store procedure stuff. And I don’t know. Maybe. Maybe I’ll surprise. Maybe.

Maybe I’ll even surprise myself. It’s hard to. Hard to tell what’ll happen there. Anyway. Cool. La la la la la. Thank you for watching. Goodbye.

Going Further

Things I Wish Inline Table Valued Functions Helped With In SQL Server

Posted on April 2, 2025January 22, 2026 by Erik Darling

Things I Wish Inline Table Valued Functions Helped With In SQL Server

Video Summary

In this video, I delve into the disappointment surrounding inline table valued functions in SQL Server, particularly focusing on their limitations and how they fall short of expectations. I explore why these functions don’t adequately address issues like local variables and kitchen sink predicates, which often lead to suboptimal query plans despite their potential benefits. Through practical examples, I demonstrate how even with indexes in place, inline table valued functions can still result in inaccurate cardinality estimates when dealing with parameters or local variables, leading to inefficient execution plans.

Full Transcript

Erik Darling here with Darling Data. In this video, we’re going to talk about where I feel disappointed by inline table valued functions. Now, every so often, you know, granted, inline table valued functions in general are my preferred mode of user defined function in SQL Server because Scalar UDFs, despite the Scalar UDF inlining feature, they’re like, that obviously can’t fix all of them, and multi-statement table valued functions, which return the results of a table variable, those two types of functions often have many, many performance issues. My, my disappointment with inline table valued functions mostly comes from the things that they don’t address that they seem like they would be a good vehicle for. So things like, you know, local variables, things like, like kitchen sink type stuff, like you would, you would just hope like that there was be some better way of dealing with that stuff, then like the current tools and methods that we have, but inline table valued functions, don’t give us a way to, to deal with that. So I’m going to talk about that in this video. Then if the slide will kind move forward, thank you. And if you can ask for that in a bit, if you’re going to ask for that, to deal with that. So if you want to ask for that in a bit, if you’re going to ask, but what are you’re saying?

If you would like to support me and Bats coming up with this sort of content for you, then you can do that. There is a little button that Bats is pointing to, or rather a link, where you can become a paid member of the channel. And for as low as $4 a month, you can help keep my eyebrows.

In good shape. If you do not have $4 a month, perhaps you have your own grooming routines that take up the majority of your disposable income, well, you can always cut your fingers off. You can like, you can comment, you can subscribe.

That also gives me all sorts of warm, fuzzy feelings. It does not do much for eyebrow shaping, but still feels pretty good. If you want to ask questions privately that I will answer publicly during my Office Hours episodes, you can do so.

That link is also available for you in the video description, and it’s a good time for everyone. If you need help with SQL Server, boy, do you. Let me tell you how much you need help with SQL Server.

More than I can fit on the screen. I am available for consulting. Believe it or not, I do all of these things at a very reasonable rate, and according to many of our nation’s finest publications, I am the best SQL Server consultant in 75% of the Earth’s hemispheres when it comes to performance tuning.

If you want some awesome SQL Server performance tuning training, well, golly and gosh, don’t I have it. I have about 24 or so hours of it. You can get it all for about 150 USD with that discount code right there.

And of course, coming to you live and in person, SQL Saturday, New York City, 2025, May the 10th, with a performance tuning pre-con on May the 9th with Andreas Walter teaching us about performance tuning stuff. I will be there handing out lunches, making sure that everyone’s happy, and I don’t know.

Maybe this will be my chance to return to bouncing. Maybe I’ll work security and just sit there and stare glumly at people and every once in a while just walk into bathrooms and make sure there’s only one set of feet in the stall.

It’s a hard job. Anyway, let’s talk about inline disappointments here. Now, I’ve got an index that I’ve already created on the post table.

It’s a great index, maybe the best index I’ve ever created. It’s basically all we need for this example. And I’ve also got a first inline valued function here, where we’re going to talk about my first disappointment with inline table valued functions and that they don’t really help with local variable problems, right?

So if we run this query here with a literal value, Siegel server is just like you would expect, is able to take that literal value and apply it as an index seek and do accurate cardinality estimation.

If we hover over this, you’ll see that that is actually passed in as a literal value. This might have some foreshadowing for future demos, but let’s not get too far ahead of ourselves. But if we run this for other values like three, or that’s a two, Eric, fingers, fingers, we will get a plan for that with accurate cardinality.

So with literal values, this query runs, even though this is a parameter up here, when we pass a literal value in, SQL Server is like, dope. I got it.

I can figure this out. But as soon as we start doing things where we declare a local variable and set that equal to something, SQL Server, even though it is still able to seek into the index, now we start getting these wacky cardinality estimates.

And it doesn’t matter what we change this to. Like, again, fingers, three. We will get our, like, 160-something rows back, but SQL Server will still guess this number of rows, which is probably not the greatest thing in the world, right?

This is like, why can’t you just pretend everything’s a literal? Why do you have to do this to me? So that’s no fun there. Another, well, what was I doing?

These two, right? Yeah, we still get, we get the wacky cardinality estimates for both. I should probably change these to the same number so that it makes a little bit more sense, right? So we do this.

We start getting the wacky cardinality estimates even from the inline table valued function. If I change both of these to three, we’ll get the same thing. Now, where it gets a little disappointing is with the, is with parameters, because you would want SQL Server, I mean, maybe, to, like, you know, be able to use inline table valued functions and maybe, you know, not give you parameter sniffing problems.

But unfortunately, they do not help us get around this either. If we run this first for three and we look at the cardinality estimates, we see one, six, seven there. And if we bump this back up to four and we get the slightly higher number of rows back, then we will still be reusing the cardinality estimate from the previous execution.

This is just one of those things where, like, you would hope that, like, you know, something inline that returns a select would just do a little bit more for you. But we just don’t, we don’t get any of that, we don’t get any of that good stuff out of it.

Maybe there should be a fourth class of function that handles this sort of thing. I don’t know. There’s just, there’s just so few good ways of handling things. You just, you just hope that something else will, will reach out and save your day.

We’ve also got this function down here called no optionals, right? And this is going to sort of give us our kitchen sink style setup. But just like with, with other stuff, if we, if we run this query, SQL Server takes that literal value and everything is fine here, right?

Everything’s all good. But as soon as we go to declared variable, we end up with a not so hot thing going on. We end up with a very typical kitchen sink predicate.

Instead of a seek, we scan the, we scan that index. You can see very clearly that is an index scan right there. And that is, that is not, that is not a good time. That is not what we wanted.

And of course, if we were to parameterize the query, and let’s say we ran this first for three, not only would we get the scan, and not only would we get the previous cardinality estimate. Oh, because you know what?

I didn’t clear out the plan cache. So if we run, let’s go back in time and do this for four, which is, I guess, the cache plan for this one currently. I did not free the plan cache for this one. We get the correct cardinality estimate from this, but we still scan, right? So SQL servers can still do cardinality for that.

But then if we switch this back to three, we will, we will retain the cardinality estimate and we will retain the scan. But you know, the, the, the, the estimate will not change for this, which is kind of disappointing as well. The only way to get, the only way to get that would be to either use a recompile hint, right?

Which, you know, is a pretty common way of getting around the kitchen sinky stuff. We go back to using the seek because, because SQL Server can, you know, just infer this as a literal value, right? We get that embedded in the query plan rather than relying on parameters.

The other way to get around that is to write somewhat unsafe dynamic SQL, where we concatenate this into the string. And of course, since, you know, it can be a little more forgiving on this because we are, we are still using a, a, a placeholder, a variable or parameter that is typed as an integer. So you can’t put like drop table something in here.

But, you know, you’re still not going to be great. I’m still not crazy about unsafe types of dynamic SQL. The other way of getting around this is to embed a literal, embed this in there is something concatenate this into the string and then run the query like this. So is this the end of the world?

No, it’s just kind of a bummer because, you know, like I said, you just want something other than like, you know, writing a bunch of tedious wrapper store procedures or writing a bunch of tedious dynamic SQL. And that’s a good example to give you some, like just some break from like these types of problems and queries. And you always hope that like, you know, like things like inline table valued functions, which have so much good use and application and can help with a wide variety of problems caused by other types of functions.

that, you know, like they would just give you some respite from these other things, but they don’t. And this is something that I do have to, you know, explain to clients a bit, which is why I’m talking about it here. But, you know, it’s just kind of a sad face for me.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something, even though, you know, it’s hard to enjoy yourselves when you’re being disappointed. It’s a little difficult to maintain enjoyment when disappointment is up here.

Enjoyment tends to fall off down here. But, yeah, just, you know, it doesn’t work is, I guess, my point in all this. Anyway, I’m going to go hopefully figure out something less disappointing to talk about.

So, anyway, goodbye.