Learn T-SQL With Erik: Cursor Loops Are Better Than While Loops
Video Summary
In this video, I discuss the merits of using cursor loops over while loops in T-SQL programming, sharing my personal belief that cursor loops are often easier to write and maintain. I walk through a basic example of how to use cursor variables in SQL Server, highlighting their self-cleaning nature and demonstrating how they simplify the process compared to manually managing row increments with while loops. By comparing this approach to yesterday’s video on while loops, I illustrate how cursor loops can be more efficient both mentally and for database performance, especially when dealing with non-contiguous data sets.
Full Transcript
All right, Erik Darling here with Darling Data, as the sign indicates. And today we are going to talk about how I think, it’s my personal belief, that writing cursor loops is much easier than writing while loops. So we’re going to talk about cursor loops in today’s video.
This is of course part of my little itty-bitty teaser bits of my Learn T-SQL course. If you would like to buy that course, hire me for training, become a supporting channel member, or even ask me office hours questions, all of those helpful links are down in the video description right below this.
So I would encourage you to click on those, should you be interested in pursuing our relationship further. And, tilly You feel so kind.
If you’re feeling so kind, you could always like, subscribe, and tell a friend about this channel. Of course, I need to leave the house a few times this year for the Pass on Tour event and, of course, for Pass Data Community Summit. I will be in Dallas September 15th and 16th, Utrecht, October 1st and 2nd, in Pass Data Community Summit in Seattle, November 17th to 21st, where I have not one but two magnificent days of pre-cons with Kendra Little all about T-SQL.
We’ll be covering some of the material that I’m going over here. So with that out of the way, let our database party begin. We will get right into our cursor loops.
Now, like I said in the intro to this video, I really do believe that once you learn how to write a cursor, it simplifies a lot of things that you might have to consider and try to be smart about when using a while loop. So I think probably the best way to write a cursor in order to simplify even that part is to use what’s called the cursor variable.
And the nice part about cursor variables is that you don’t need to close or deallocate them. They automatically are self-cleaning, which I think is nice. So what I’m going to do is just run through a basic cursor loop to just populate some data and just kind of show you like the basic fundamentals of it.
And then I’m going to compare or rather I’m going to do a demo like in yesterday’s video with while loops, where things are sort of greatly simplified for us over needing to think about how to increment and how to find what to work on next. So I’ve got my cursor stuff declared up here.
Zoom, it would be so kind as to actually zoom. We’ve got this stuff up here. And then I’m going to be using a table variable to hold some of our data because I’m just putting a single row at a time in there. I don’t really care so much about the performance of this.
So the table variable is just fine for us here. And then I’m going to set the cursor variable with the options that I want and also tell the cursor variable what the query that populates the cursor looks like. That’s this portion down here.
I talk way more about the cursor options in the full class. So if you’re curious about these, well, please consider purchasing. And then we will open the cursor.
And while that cursor is open, we will do some fetching into the cursor. And then we will, you know, populate some stuff. And what we’re going to do is what I have in here is some additional stuff to kind of show you how the cursor makes progress through all of the values and stuff.
Because what I want you to see is how the fetch status changes as the cursor finishes. And then we will be looking at the results down here. So after I finish highlighting this gigantic loopy piece of code, we’re going to run this.
We’re going to get a result back very quickly with all of the data that we populated our table variable with right here. Over in the messages tab, though, this is where we’ll see sort of how the while loop, rather the cursor loop made progress. And how this is a little bit easier than the while loop equivalent.
See, we didn’t have to tell the cursor loop what row it was going to get next. Like, we didn’t have to increment some value and then go find that value. The cursor loop is based on this query.
And this query here just goes and finds the next thing for us to do anything with. Actually, the cursor keeps track of this position for us. So we got all of the stuff that we wanted.
And of course, while the fetch status was zero, which is for all of these iterations through, we went and we got a new row to work on, worked on what we needed to do. And then at the very end of the while loop, the fetch status changed to negative one. That means that the loop killed itself and we didn’t have to tell it to do anything.
So this is how, one way how cursor loops can be a little bit simpler to write once you understand the basic syntax than writing while loops. Now, to compare the cursor loop to the while loop in yesterday’s video, there were lots of things that I had to do in yesterday’s video in order to figure out like a starting and ending position for the while loop. How to increment data, how to increment to get the next row in the while loop versus like, not like doing a naive incrementing, just like adding one to the next ID to get, having to think about how to increment things so that I was making sure that I got the next value.
So I’m going to show you how this is simplified for the cursor loop. Now, in yesterday’s video, I showed you like both a naive and then a smarter way of doing things. But in the smarter way of doing things, of course, was more efficient.
But of course, in this case, we can get more efficiency without having to worry about thinking about stuff too much, which is nice because we can now think about other things like where to go for dinner and what sort of wine to drink. And I don’t know, like where we want to go on vacation next. We’ll become more efficient people.
And so we have better things to do with our minds than figure out what to do in a while loop, right? Which is not fun. Which is not making good use of time, right? It’s not a productive use of our brains, whereas thinking about food and wine and vacations, terribly productive use of our brains.
So what we’ve got from this table is, of course, the same non-contigious situation as we had yesterday, where like the IDs that we’re working off do not just count up like one through ten or whatever, the way that the row number function does. The row number function rows are over here. So we can see that we have some big gaps in a lot of this between what number is next or not.
So what we can do to write a cursor to do this is pretty much what we did before. But I want to show you just how like we don’t really have to do as much setup for this, right? We don’t have to find a min and a max ID.
We don’t have to decide how to increment. We don’t have to like do a lot of pre-work to figure out our cursor starting place, current position, and ending place. The cursor does that for us.
So let’s run this. And I am going to show you some of the sort of guts of what happens as we run through this. And just like in the last demo, we’ll see that the cursor does a great job of, you know, while the fetch status is zero, just going and finding the exact next row that we needed based on the query we gave it to populate the cursor.
So this is all very nice. And we go through and we don’t like double work anything. And we don’t have any sort of no ops where we like maybe incremented by one and found a row that didn’t exist.
And then at the very end, after we hit 100, we have our fetch status of zero. This does double print, but it does not double work 100 here. It’s just a double printing thing that I don’t know.
I’d say I’d fix it in post, but we’re already posted. So anyway, running through the cursor loop like that, we end up with no dupes, right? Every row has a count of one.
Whereas when we did the naive looping with the while loop yesterday, we had a lot of dupes where we were just like finding where we’re either working with the same number over and over again, or we would have double worked and sort of no opt things. So cursor loops, usually much more efficient mentally and I guess in the database physically than writing while loops, especially if you do not write while loops in a way that intelligently goes through your data and finds what to work on next. So I do hope that you will perhaps spend some time learning how to write effective cursors in T-SQL.
It can be done. I know that cursors get a lot of bad press for performance and that is true. Like cursors can perform poorly when compared to sometimes set-based operations.
But even sometimes cursor loops versus while loops, cursor loops are generally on the more efficient side, at least for us as the writer and maintainer of the code. And especially on the database side, where if we make a mistake with a while loop, we could pay quite a penalty in the database. Whereas if we make a, you know, we can’t, we don’t have the ability to make that mistake with a cursor because the cursor is keeping track of things for us.
And, you know, computers, pretty darn good at keeping track of things. Anyway, that’s it for me. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you, well, this is going out on Friday. So the next video will be office hours. And I realized in the last video that I accidentally only copied four questions. And I’ve maybe lied about answering five questions.
So, I don’t know. Should I do six questions in the next one? Or should I just do the normal five? I haven’t decided yet. I don’t want to set a bad precedent here.
Start miscounting and playing catch up and all that. Anyway, thank you for watching. All right. Goodbye. Happy Friday, too. Because you should at least get one of those in your life. All right.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.