When Function Rewrites Need Query Rewrites In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into a tricky issue involving query rewrites for inline functions in SQL Server. Specifically, we explore the challenges that arise when rewriting scalar UDFs to inline table-valued functions and integrating them back into existing queries. The process can be quite frustrating due to obscure errors like “Aggregates on the right side of an apply cannot reference columns on the left side,” which require unconventional query rewrites using CTEs or derived tables to resolve. Despite these quirks, understanding how to navigate such issues is crucial for optimizing performance and maintaining clean, efficient SQL code.
Full Transcript
Erik Darling here with Darling Data. In today’s video, we’re going to talk about query rewrites for inline functions. And I know this is kind of a hard one to title because it doesn’t have a very apparent title. We’ve spent a few videos talking about rewriting scalar UDFs to make them inline functions and some of the weird stuff you have to do to get that working sometimes. But this one, there’s a very, the function rewrites for the function rewrites for the function rewrites for the function rewrites for the function rewrites. What happens though is when you start using the inline version of the function in the query the way it was written before, you start getting weird errors. And we need to avoid those errors because no one likes errors. You would think this sort of thing would be easy, but it’s a database. So nothing is easy. That’s why I have the problems that I do in life. Anyway, if you like this channel, you can join the nearly 25 other people who support this channel by signing up for a membership. If assuming you’re okay with parting with like four bucks a month, if you’re not okay with parting with like four bucks a month, you can like and comment on the videos and you can even join the over 5000 other data darlings out there in the known universe and subscribe to the channel. So you get notifications whenever I publish a video. It’s a great trade. It’s an awesome trade off. You get a ding and a video and I get to sweat under hot lights. Everything’s coming up you. If you need help with your SQL Server, I am the best SQL Server consultant in the world. A lot of my clients have worked with other SQL Server consultants, thrown their hands up after not getting results and come to me and gotten results. It’s wonderful being able to do that. It’s also very fun seeing the login names from other consulting companies in the world still on those SQL servers.
Hi out there. If you would like some very high quality, very low cost SQL Server training, you can get all mine for about 150 US dollars a month, not a month for life. It’s not a month. I don’t know why I said that. I think what I was going to do is tell you I don’t have a subscription where I charge you a month by month or year. It’s for life. You can get it all. You can either go there and use that code or you can just click on the link in the video description if you’re feeling particularly lazy. And you’ll end up at the site. With the coupon code applied. It’s magical. Since this will be airing after Past Data Summit, I have no upcoming events. If you have an upcoming event, let me know about it. Maybe I’ll come to your up event. I don’t know. We’ll find out. Anyway, with that out of the way, let’s finally do this. Let’s say that we have a scalar UDF. It looks something like this. It’s really not a big deal. Everything’s fine there. Everything is scalar UDF-y. Everything in there works.
And when we run the query, we get a particularly UDF-y plan. It’s a query that has all of the hallmarks of scalar UDF problems in it. This will run for a couple more seconds. And if we look at the execution plan, we’ll see that we have a compute scalar that sucks up the majority of the execution time in this query, right? Almost 11 and a half seconds of time. Well, I guess minus a little bit from over here. So I guess about 10 and a half seconds of time.
And then if we get the properties of this, we will have this fun warning over here. We will have this. I’m sorry that the properties window showed up like that. That is unintentional. We will have this non-parallel plan reason yelling about our scalar UDF. Now, I know what you’re thinking out there. Ah, SQL Server 2019, UDF inlining. I know. The thing is, not a lot of people are in compat level 150.
And even if you’re in compat level 150, there are still a lot of scalar UDFs in the world that cannot be inlined automatically by that feature. So there’s still a lot of UDFs out there to rewrite. Trust me, I know. I rewrite a lot of them. That’s another reason why I have many of the problems that I do.
So this is a fairly straightforward function to rewrite to be an inline table value function, right? This is just fine. You just do the same thing with returning a table rather than returning the value like you do up here, right? So whatever. Not a big deal. The problem is, if you try to stick that in the select list the way that you do, or the way that we did with the scalar UDF, we get this error. And it’s a very obtuse error.
Aggregates on the right side of an apply cannot reference columns on the left side. Well, I don’t see any applies in here. We can’t even get an estimated plan to see if there’s an applied pattern somewhere in there. It’s just, you know, where we have a subquery and that’s, I don’t know.
Like, you couldn’t write it as cross apply because then it would definitely be an apply. Or even order apply would still be an apply. Apply is the key word there. So there are two ways that you can fix this, rewriting the query.
And they’re both stupid. You shouldn’t have to do this. This is asinine. Asinine to the very core, to the back teeth, as some might say.
Okay. So you can either use a CTE and you can do the aggregates here and then pass the aggregates to the function here. And guess what? This will work just fine. We get a much faster parallel execution plan.
So this is okay. And if you’re like me and you just like to use derived tables because you don’t want to use CTE because you don’t want anyone to get the impression that CTE are really any better than derived tables, you can do that too.
And you can get the same fast parallel execution plan. Ain’t life grand. So if you’re rewriting a scalar UDFs and you run into that error where SQL Server is like, oh, no, apply, right side, aggregates, blah.
All you have to do is rewrite your query a little bit to do the aggregates from something in something and then select from that something. And those aggregates magically work in your inline table valued function.
Who would have thunk it? Crazy out there. Anyway, I’m going to just go cry now. I wish I was good at something other than databases.
Maybe I’ll get discovered on here for something else. I don’t know. If anyone needs a young, handsome talk show host, I’d be happy to talk to Hollywood celebrities for almost any amount of money because they’d probably end up liking me a lot and bringing me into their inner circle, most trustworthy friends, and go to cool parties in the hills.
They could buy to all these pains, these sufferings. 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.