Simulating WAITFOR In Scalar UDFs In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into an intriguing and somewhat tricky aspect of SQL Server functions: making them wait for a specific amount of time. Erik Darling from Darling Data explores how to implement such functionality within user-defined functions (UDFs), which isn’t straightforward due to the limitations imposed by SQL Server. However, with some clever workarounds and insights from Alexander Kuznetsov’s blog post, we can achieve this through while loops that essentially create a delay. I walk you through creating these functions and demonstrate their use in practical scenarios, showing how they can be used for various sneaky and interesting purposes. If you’re curious about the full extent of what can be done with such functions, mark your calendars for Seattle to attend Past Data Summit where we’ll dive deeper into these and other advanced SQL Server techniques!
Full Transcript
Erik Darling here with Darling Data. In this video, we’re going to talk about something exceedingly tricky that you can do. It has to do with having functions wait for a specific amount of time. We’re going to talk more about this. It’s really hard to do a pithy intro here. But we’re going to talk about it. But before we do, we have some things to talk about. Like this channel. If you would like to sign up for a membership to this channel for as low as $4 a month, you can do that by clicking the Become a Member link in the video description. If you don’t have $4, even for one month, perhaps that just cuts into the ramen budget a little too heavily. You can do all sorts of wonderful free things that let me know you care. You can like my videos. You can comment. on my videos. And you can subscribe to the channel. I do like seeing all those things. It brings me a very specific type of joy. If you need help with your SQL Server, probably not anything that we’re going to be talking about today, but I am a consultant and I do some things with SQL Server very well. I don’t set up availability groups. I don’t really sit there and mind your backups. I don’t want to talk about capital R replication. But I can tell you, if your SQL Server is healthy, I can tell you if your SQL Server is as fast as it could be. The answer is no. And I can do all sorts of other things like make it faster. I don’t know. Some people enjoy that. Some people prefer that.
I can even reduce your cloud bills. How about that for a sales pitch? You want to give less money to Microsoft or Amazon? Call me. We can do that together. If you need some high quality, low cost training, you can get all 24 hours of mine for about $150 USD by going to that link up there and then using the discount code springcleaning. There is, of course, a link to automate all of that wonderfulness in the video description as well. So, and probably at this point, this might be past Data Summit. I don’t know. Maybe a little bit before, but you can still catch me there, November 4th and 5th. If it is past November 4th and 5th and you didn’t go to Seattle to pass Data Summit, you missed it. Sorry. Can’t do anything to help you there. But runner-up prize is if there is a SQL Saturday or Data Saturday or whatever Saturday event near you that is in search of a pre-con speaker, let me know. I will do my best to get pre-coned there.
But with all that out of the way, let’s talk about what I want to talk about, which is how you can get a function to wait for you. So, I realize that the logic in this function is not complete. Right. It just says, if delay is greater than zero, do this thing. Otherwise, we’re going to have sort of whatever in there. We could, of course, fix that with like, you know, putting 0000000 in there.
And then it would only change if delay was greater than zero. Otherwise, we would wait for zero seconds. Maybe that is enough. Actually, this function is now Turing complete. We’ve done it. Good job, us. But the problem is that if you try to do this in a scalar UDF, we’ll get an error message. It’s saying the invalid use of a side-effecting operator wait for within a function. That’s no good, is it?
We seem to have hit a wall here. Hmm. What can we do? What can the clever and devious mind do?
Well, a very clever and devious mind, long before I started thinking about this, actually had an example of what you can do. Smart guy named Alexander Kuznetsov. Kuznetsov. Something else. Probably I was pretty close on both of those. Left SQL Server for Postgres around 2013 or 14. Hasn’t been heard from since. Just kidding. He’s doing his thing.
But actually, maybe now that Pass has a Postgres corner, he’ll be back. I would love to give him a very big hug, probably out of nowhere and terrify him. But anyway, a long time ago, he wrote a blog post about scalar UDFs and he actually did the hard work for me.
And all I had to do was find a link to the hard work because everything is in the Wayback Machine now. But what you can do in a function is a while loop that for a, you know, you declare all this stuff and you set a delay in the function input. And this is just the default. You can change this, of course, when it actually runs.
But then you say while the current date time is less than that thing, you just, you know, run this stupid loop thing. And all you’re doing is setting a bit to null over and over again. So it’s very, very little work. But we can test that out and we can say, let’s just make sure this function is actually in there and created.
Sometimes weird things happen. Who knows? Who knows SQL Server? But if we say wait for three seconds here and we keep an eye on the clock that’s sort of next to me over here, you’ll see that that waited for exactly three seconds and then returned our column, right?
That’s pretty cool. And we can also have that, we can also have that act as input from a select list. So if I say select one, union all, select two, union all, select three, this function will wait for one second and then two seconds and then three seconds.
And we can, we can actually test that out by running this. Did I run that? No, I didn’t. I just hit R. Good job. Finger was off by one.
But don’t worry, this function will run for exactly, sorry, six seconds, right? To return those three rows. Now what can you do with something like this? All sorts of interesting, sneaky, outrageous things.
But you’re going to have to come to Seattle. You’re going to have to come to Past Data Summit in order to see all of those sneaky, outrageous things in action. So I suggest you buy your plane tickets now because it’s getting kind of late in the day.
It’s time to boogie. So anyway, thank you for watching. I hope you learned something.
I hope that you will be titillated to the point of travel by what I’ve discussed here. And you’ll be looking forward to seeing just how many awful things you can do with a function like this in SQL Server. Because trust me, there’s a lot.
Anyway, thank you 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.