The Weird World Of SQL Server API Cursors #tsql2sday

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

---

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.