Learn T-SQL With Erik: Writing Smarter While Loops

Learn T-SQL With Erik: Writing Smarter While Loops

---

Video Summary

In this video, I delve into the world of while loops and share some insights that might surprise even seasoned database professionals. We explore how using `continue` and `break` can alter a loop’s behavior in unique ways—like skipping numbers or quitting early when bored with repetitive tasks. Additionally, I discuss the importance of proper incrementing to avoid inefficiencies and ensure your loop processes each value only once. This is crucial for maintaining database performance and avoiding redundant operations. The video also touches on cursors as an alternative that can simplify loop logic and handle increments automatically, making them a valuable tool in your T-SQL arsenal. Whether you’re just starting out or looking to refine your skills, there’s always something new to learn about effectively communicating with databases.

Full Transcript

Erik Darling here with Darling Data, and we are going to continue our T-SQL learning because that’s what we like to do around here. We like to learn about databases, and more importantly, we want to learn how to communicate more effectively with our beloved databases. They are important things in our lives, and we should be able to talk to them openly and honestly about things. So that’s what we’re going to keep doing. That jumped way too far ahead. If you like this material and you would like to buy this full training course, you remember these are just little bite-sized snippets of things. Down in the video description, there are links for all sorts of things that you can do to interact with me, like hire me for consulting, buy this training that you’re seeing little pieces of, become a supporting channel member, ask me questions for my Office Hours episodes, wow! And of course, the usual like, subscribe, tell a friend if you feel like your friend is having me, and difficulty effectively communicating with your database partners in their lives.

And maybe they could learn a thing or two over here and have healthier database relationships. The nice folks at Red Gate are putting me in front of full public display via the Pass on Tour and Pass Data Community Summit events. We’ve just wrapped up the New York event, so now we’ve got Dallas in September, the 15th and 16th, Utrecht in October, the 1st and 2nd, and Pass Data Community Summit in Seattle, November 17th to 21st, where I will have two days of T-SQL pre-cons with Ms. Kendra Little.

And that’s going to be great because then everyone in Seattle is going to know SQL and maybe that’ll help clean up some of Seattle’s well-known maladies out there. But anyway, let’s, let’s, let’s get on with our database party here and let’s, let’s go talk about while loops. Now, there are a couple of things that you can do in while loops that I learned probably a little bit later in my data database career than I feel fully comfortable saying, or fully comfortable admitting to, but that’s okay. You know what? We all learn, we all grow. It’s fine.

So the, the two things that I learned about probably, I, I, no, like continue, I, I, I learned this one, this one came along way later. I didn’t realize this was a thing until I saw someone else use it and I was like, continue, what the hell? But instead of break, I would often just use return instead, but you know, six of one, half dozen of another.

But continue is neat because continue will bring us back to the first begin in the, in a while loop, which is, I think a neat thing to be able to just say, ah, I’m done here. Let’s go back to the beginning. And of course, break will end things early. So let’s say that we have our, our task is to count to 100, but like many people in the tech world, we do not have the patience for repetitive tasks.

So I don’t know, will we get to a hundred? Probably not. Are we, are we, are we going to skip some numbers? We probably are. But this is one way that you can sort of use continue and break in a loop to, you know, make the loop behave maybe a little bit differently than just cycling through and counting from one to one. 100. For example, we can use continue to, let’s say, count up to nine or every time, or every time we get to a new 10, rather a new number that with a modulo of 10 equals zero.

We can just add on 10 and skip ahead a little bit because it gets really boring. And then maybe we might decide that, I don’t know, we’ll get to around 89 or so and we’ll, we’ll just quit. And that’s what our loop will do. Every time we hit a new 10, we’ll skip a few because we got bored.

And then when we get down to the end of our loop, we will say, oh, I got tired. Sorry, not happening for us. So these are neat things, continue and break. And also, you know, I guess return and break would be pretty similar, but you can use continue and break or return to sort of control the flow of a loop a little bit in slightly different ways.

But one thing that is important to consider when dealing with a while loop is, of course, how to increment through things so that you maybe don’t get stuck on repetitive tasks in a loop. So this is something that I talked about in one of the other videos earlier this week, where if you assume that whatever task you’re doing within the loop is working with a, like a perfectly sequential number, then you might run into situations where either you don’t find a next value or something else you do in the loop means that you just keep like you might attempt to run the loop for values that aren’t actually there.

So like if you just say, if you’re just always incrementing whatever loop, whatever is driving the loop by one, you can end up in situations where your loop is sort of ineffective or doing a lot of repetitive work in places. So we’re going to create some tables here for a while loop to work off of. And we’re going to stick some data in these tables.

And this is the somewhat boring part, but that’s okay. We have, we have, we have more interesting things to think about. So we’ve got some data in our worker table. And if we look at the what’s in the worker table and we start looking at the gaps in here, keep in mind that like the ID values that we put in, like, like they do, they are not perfectly sequential.

This goes like where it should, like, you might be expecting it to go like from like one, two, three, four, five, six, seven, eight, nine. I guess I should have gone down to 10 there. Here we’re jumping around quite a bit.

It’s like one, four, six, seven, 10, 11, 13, so this is not a perfectly sequential set of numbers that we’re working off of. This is something that you need to think about quite a bit when you’re writing while loops. And for the reasons that I said before, you don’t want to either do repetitive work or do essentially a bunch of no ops while you’re running through things.

So if we go and we run this while loop doing, let’s, let’s call it naive incrementing, where all we do is set the value plus equal one, then this will run through and do things. But it’s not really going to want, but it’s not really going to do things the way that we want. Because now if we look at what we actually processed, we will end up having with a bunch of like having processed a bunch of dupes until we found other rows, right?

Like these are all values, all of these I values are values that we ran multiple times through the loop until we found a new value. So this is not a good time, right? We ended up like processing parts of this loop either too many times, or like, you know, if we maybe there was like a no op sort of thing, we would have just like been spinning in a circle doing like no work really.

And that’s, that’s not good, right? We don’t want to write inefficient loops because loops are already sort of inefficient generally. You know, like loops and databases, usually not the first thing you want to use. They’re generally set based things you would want to use instead.

There are of course perfectly good reasons to use loops, especially to like batch modifications, but like batch units of work. But you know, we don’t always want to just jump right to loops, right? We want to make sure that we are using the loop for the right reasons and then in the right way.

So if we were to change the way that we increment the loop, and rather than just saying like to increment the loop by one, if we were to set the, if we were to drive our loop by using whatever increment value we found for the last time around, then we would have a much tighter loop that actually does like significant meaningful work on each trip through.

So rather than just add one, we’re going to add whatever increment we find in there. And this loop will have done a less work because you wouldn’t be meaning, meaninglessly spinning through things. But we would have absolutely no dupes processed in here either.

Rather than having like all bunch of fours and threes and fives and stuff. We just ended up, we just, we only process each value once. So each time through the loop, we got a brand new value set to the value that we should be using for the next turn through the loop.

So just be very, very careful when you’re doing this. In the next video, we’re going to talk about using cursors for something similar, but also how cursors do a lot to simplify loop logic. If you are writing a very, like a traditional while loop like this, you know, cursors in these cases will do just about the same performance wise.

So if you’re already doing a loop where you’re incrementing through things and doing units of work, a cursors is not really going to be worse. But cursors actually do a lot to simplify the logic required to set up and run through the loop. And to even help you not have to figure out what you need to increment to because cursors will keep track of that for you.

So there are a lot, in a lot of ways, it pays to learn how to write cursor syntax over a while loop, because the cursor syntax will actually do a lot of the thinking for you. And you don’t have to get into some of the like, you know, nuances of while loop handling and how you want to go through the while loop, because cursors will will fix that.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video about our beloved cursors. So I look forward to seeing you there. Anyway, thanks for watching.

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.