Tricky Scalar UDF Rewrites In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the intricacies of rewriting scalar UDFs in SQL Server, a task that can quickly turn from routine to headache-inducing. Specifically, I explore an interesting edge case where a scalar UDF that was guaranteed to return a result ended up as an inline table valued function (TVF), which altered its behavior unexpectedly. This change not only affected the results but also introduced new challenges in how the function needed to be called and handled. I walk you through the process of rewriting this function, highlighting the pitfalls and solutions encountered along the way, such as dealing with null values and ensuring that the rewritten function returns the expected results. By sharing these insights, my goal is to help you navigate similar scenarios smoothly and avoid the frustration that comes with unexpected changes in SQL Server functions.
Full Transcript
Erik Darling here, looking, trying to look tough, trying to not look crooked, trying to hopefully work out that scoliosis with Darling Data. And in today’s video, because I feel like I’ve had to write a lot, write, I feel like I’ve had to record a lot of these videos lately because as I go back through my notes and things, I realize just how annoying rewriting scalar UDFs can get. Today we’re going to talk about a really interesting edge case where a scalar UDF that was always guaranteed to return a result ended up as an inline table valued function, which even though it would always return a result, would not always return exactly what the scalar UDF did. And if you want to be the person who looks heroic doing this sort of thing, you need to make sure that you return the correct or the expected results when these things run.
If you like my material and you have four extra dollars a month, you can subscribe to my channel with a membership using the link down in the video description. I’m trying not to, it’s probably over that, somewhere in there. If four dollars a month is too rich for your blood, if you’ve, you know, got better things to do with your money, then I understand.
Liking, commenting, subscribing, all perfectly wonderful free things you can do to keep me motivated to keep creating these videos. Because we’re hanging on for dear life here. If you need help with SQL Server, these are all things that I am better than everyone else at.
So if you need help with any of this stuff, you can hire me for a reasonable rate and get better help than you can get from anyone. Anywhere. On the planet.
Beer Gut Magazine said so. If you would like some high quality, low cost content for about 150 US dollars for the rest of your life, you can get that from me. By going to that link and using that coupon code.
And guess what? There’s a link for that too. Sorry. Link for that too. Down over that way, somewhere. Not in my area, in that area.
Of course, for as long as humanly possible, I will be in Seattle for Pass Data Summit. November 4th and 5th with Kendra Little tag teaming two days of performance tuning, titillation, and top notch shoe things. Anyway, let’s get on with the show.
So, let’s look at function rewrite annoyances. And what I’m going to do is show you the initial function or a close enough approximation of the function that I was dealing with. And, you know, just because I have a newer SQL Server version, and I don’t want scalar UDF inlining to kick in because that will break the demo.
I’m just declaring this thing in here so we have a date time. Right? So, it’s a scalar UDF.
What this thing does is it declares B, which is a bit, which is false, which if you ever run this, it will be zero. Excuse me. And what we do is we select B equals case when all this stuff, blah, blah, blah, blah, blah.
And then we return B. The thing is, if we select this here and no row gets returned, B doesn’t change from false to null. It stays false.
So, when we run this query, what we’re going to get back is a whole bunch of zeros from our scalar UDF. All right? So, you look at this query here. Thing zero equals the scalar UDF that I just showed you.
Okay? Simple enough. This is also a simple enough UDF to rewrite. Or so it seems. Bah, bah, bah, bah. It’s terrifying.
Now, if we run this, which rewrite, or rather writes a new function. It doesn’t rewrite that function. This is the rewrite, not rewrite.
See? It’s different. This will return a table, which is the type of function that does not cause performance to throw up. And if you look at what this does, it is nearly the same thing, but without declaring a variable or anything else like that.
So, this is where things change, right? Because now we’re not setting, we don’t have a variable called B. We can’t declare a variable called B in an inline table valued function.
So, now we’re just setting B to this. And if B doesn’t turn out, B is null in here. Then, B is null in the results.
All right? So, I think I already did this, but, you know, just to make sure. We do this. And then, since this is an inline table valued function, we have to call the results a little bit differently. All right?
So, this is, we’re going to select the B column from our rewrite function. And when we do this, the results will be null. And this is not what users expect. And this is what will make users freak out. Now, I know what you’re thinking.
You could just put an is null on that. And then, if it returns null, it’ll get replaced with zero. But you’d be wrong.
That does not work out. Is is null broken? Hmm. No. No.
We just have to put wrap is null one step further out. But this is, don’t worry. This isn’t the final fixed result. This is just me showing you how annoying this can get when you’re trying to figure out what’s wrong. You could put an is null around the entire function column.
And this would finally get you a zero where you expected a zero. Right? So, just wrapping the return column from the function in is null doesn’t get you anything.
This does. But that’s not good. Right? That’s not, that’s not what we’re after. We don’t want people to have to remember to wrap an entire.
They already have to remember that this isn’t a scalar UDF. And they have to call it in like the select list like this if they want it to get used. Painful enough.
Right? Microsoft couldn’t budge on that. Right? It couldn’t, couldn’t do any better. This is what we get. Thanks. Real pal. Can’t just rewrite a function and have it get called the same way.
You got to do all this crap. So, anyway. Annoying. What I found the easiest way to get this to work correctly is, is to wrap this inside the function. We have our normal function call.
And then we have a union all to just selecting B equals zero. Right? So, this is sort of the equivalent of setting that local variable in the scalar UDF to zero. And then we get the max out here from that internal, that B column in our derived select.
Right? So, if the max is zero from the union all. Because zero is going to be bigger.
The max, zero is the max of null. Right? You have a zero and a null. Zero is the max. If we have a one because it returns a true. Or if we actually get a false back. Then we’ll get a zero back here.
But if we use this version of the function, we can just call this normally. And we will get back our expected zeros in this column. So, when you’re rewriting functions, you know, apart from the fact that, you know, making sure that performance is better.
Right? With an inline table valued function versus scalar UDF. Or even a multi-statement table valued function.
That’s really important. Right? Performance needs to get better. But you also need to make sure that the results you return match what users were getting back before. So that they don’t have this, like, new surprise result back.
Because you might have QA or unit testers or unit testing or, I don’t know, people who care about this sort of thing. And they might look at your results and say, that’s all null now. It’s not zero.
And it used to be zero or one. Now it’s null. Now it messes up this other thing. Like, maybe they were inserting this into a table. And that table doesn’t allow nulls in this column. What’s going on?
Why is it broken now? And they’re going to look at you and they’re going to think you’re an idiot. But since you’re smart, you stick with darling data, that won’t happen to you. Because now you know how to rewrite scalar UDFs into inline table valued functions and not break everything.
So good for you. It’s amazing. Isn’t it? Aren’t you glad you spent this time with me?
Aren’t you glad you invested this time in your SQL Server learning journey? I sure am. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I hope to see you again in the next video, which I will be recording, I don’t know, any minute now. I suppose we’ll find out, won’t we? Maybe God will finally strike me dead.
I never can tell what’s going to happen while I’m uploading these things. Anyway. All right. Let’s end on a cheery note. I think you’re pretty. 3, 2, 1.
3, 1.
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.