Things I Wish Inline Table Valued Functions Helped With In SQL Server
Video Summary
In this video, I delve into the disappointment surrounding inline table valued functions in SQL Server, particularly focusing on their limitations and how they fall short of expectations. I explore why these functions don’t adequately address issues like local variables and kitchen sink predicates, which often lead to suboptimal query plans despite their potential benefits. Through practical examples, I demonstrate how even with indexes in place, inline table valued functions can still result in inaccurate cardinality estimates when dealing with parameters or local variables, leading to inefficient execution plans.
Full Transcript
Erik Darling here with Darling Data. In this video, we’re going to talk about where I feel disappointed by inline table valued functions. Now, every so often, you know, granted, inline table valued functions in general are my preferred mode of user defined function in SQL Server because Scalar UDFs, despite the Scalar UDF inlining feature, they’re like, that obviously can’t fix all of them, and multi-statement table valued functions, which return the results of a table variable, those two types of functions often have many, many performance issues. My, my disappointment with inline table valued functions mostly comes from the things that they don’t address that they seem like they would be a good vehicle for. So things like, you know, local variables, things like, like kitchen sink type stuff, like you would, you would just hope like that there was be some better way of dealing with that stuff, then like the current tools and methods that we have, but inline table valued functions, don’t give us a way to, to deal with that. So I’m going to talk about that in this video. Then if the slide will kind move forward, thank you. And if you can ask for that in a bit, if you’re going to ask for that, to deal with that. So if you want to ask for that in a bit, if you’re going to ask, but what are you’re saying?
If you would like to support me and Bats coming up with this sort of content for you, then you can do that. There is a little button that Bats is pointing to, or rather a link, where you can become a paid member of the channel. And for as low as $4 a month, you can help keep my eyebrows.
In good shape. If you do not have $4 a month, perhaps you have your own grooming routines that take up the majority of your disposable income, well, you can always cut your fingers off. You can like, you can comment, you can subscribe.
That also gives me all sorts of warm, fuzzy feelings. It does not do much for eyebrow shaping, but still feels pretty good. If you want to ask questions privately that I will answer publicly during my Office Hours episodes, you can do so.
That link is also available for you in the video description, and it’s a good time for everyone. If you need help with SQL Server, boy, do you. Let me tell you how much you need help with SQL Server.
More than I can fit on the screen. I am available for consulting. Believe it or not, I do all of these things at a very reasonable rate, and according to many of our nation’s finest publications, I am the best SQL Server consultant in 75% of the Earth’s hemispheres when it comes to performance tuning.
If you want some awesome SQL Server performance tuning training, well, golly and gosh, don’t I have it. I have about 24 or so hours of it. You can get it all for about 150 USD with that discount code right there.
And of course, coming to you live and in person, SQL Saturday, New York City, 2025, May the 10th, with a performance tuning pre-con on May the 9th with Andreas Walter teaching us about performance tuning stuff. I will be there handing out lunches, making sure that everyone’s happy, and I don’t know.
Maybe this will be my chance to return to bouncing. Maybe I’ll work security and just sit there and stare glumly at people and every once in a while just walk into bathrooms and make sure there’s only one set of feet in the stall.
It’s a hard job. Anyway, let’s talk about inline disappointments here. Now, I’ve got an index that I’ve already created on the post table.
It’s a great index, maybe the best index I’ve ever created. It’s basically all we need for this example. And I’ve also got a first inline valued function here, where we’re going to talk about my first disappointment with inline table valued functions and that they don’t really help with local variable problems, right?
So if we run this query here with a literal value, Siegel server is just like you would expect, is able to take that literal value and apply it as an index seek and do accurate cardinality estimation.
If we hover over this, you’ll see that that is actually passed in as a literal value. This might have some foreshadowing for future demos, but let’s not get too far ahead of ourselves. But if we run this for other values like three, or that’s a two, Eric, fingers, fingers, we will get a plan for that with accurate cardinality.
So with literal values, this query runs, even though this is a parameter up here, when we pass a literal value in, SQL Server is like, dope. I got it.
I can figure this out. But as soon as we start doing things where we declare a local variable and set that equal to something, SQL Server, even though it is still able to seek into the index, now we start getting these wacky cardinality estimates.
And it doesn’t matter what we change this to. Like, again, fingers, three. We will get our, like, 160-something rows back, but SQL Server will still guess this number of rows, which is probably not the greatest thing in the world, right?
This is like, why can’t you just pretend everything’s a literal? Why do you have to do this to me? So that’s no fun there. Another, well, what was I doing?
These two, right? Yeah, we still get, we get the wacky cardinality estimates for both. I should probably change these to the same number so that it makes a little bit more sense, right? So we do this.
We start getting the wacky cardinality estimates even from the inline table valued function. If I change both of these to three, we’ll get the same thing. Now, where it gets a little disappointing is with the, is with parameters, because you would want SQL Server, I mean, maybe, to, like, you know, be able to use inline table valued functions and maybe, you know, not give you parameter sniffing problems.
But unfortunately, they do not help us get around this either. If we run this first for three and we look at the cardinality estimates, we see one, six, seven there. And if we bump this back up to four and we get the slightly higher number of rows back, then we will still be reusing the cardinality estimate from the previous execution.
This is just one of those things where, like, you would hope that, like, you know, something inline that returns a select would just do a little bit more for you. But we just don’t, we don’t get any of that, we don’t get any of that good stuff out of it.
Maybe there should be a fourth class of function that handles this sort of thing. I don’t know. There’s just, there’s just so few good ways of handling things. You just, you just hope that something else will, will reach out and save your day.
We’ve also got this function down here called no optionals, right? And this is going to sort of give us our kitchen sink style setup. But just like with, with other stuff, if we, if we run this query, SQL Server takes that literal value and everything is fine here, right?
Everything’s all good. But as soon as we go to declared variable, we end up with a not so hot thing going on. We end up with a very typical kitchen sink predicate.
Instead of a seek, we scan the, we scan that index. You can see very clearly that is an index scan right there. And that is, that is not, that is not a good time. That is not what we wanted.
And of course, if we were to parameterize the query, and let’s say we ran this first for three, not only would we get the scan, and not only would we get the previous cardinality estimate. Oh, because you know what?
I didn’t clear out the plan cache. So if we run, let’s go back in time and do this for four, which is, I guess, the cache plan for this one currently. I did not free the plan cache for this one. We get the correct cardinality estimate from this, but we still scan, right? So SQL servers can still do cardinality for that.
But then if we switch this back to three, we will, we will retain the cardinality estimate and we will retain the scan. But you know, the, the, the, the estimate will not change for this, which is kind of disappointing as well. The only way to get, the only way to get that would be to either use a recompile hint, right?
Which, you know, is a pretty common way of getting around the kitchen sinky stuff. We go back to using the seek because, because SQL Server can, you know, just infer this as a literal value, right? We get that embedded in the query plan rather than relying on parameters.
The other way to get around that is to write somewhat unsafe dynamic SQL, where we concatenate this into the string. And of course, since, you know, it can be a little more forgiving on this because we are, we are still using a, a, a placeholder, a variable or parameter that is typed as an integer. So you can’t put like drop table something in here.
But, you know, you’re still not going to be great. I’m still not crazy about unsafe types of dynamic SQL. The other way of getting around this is to embed a literal, embed this in there is something concatenate this into the string and then run the query like this. So is this the end of the world?
No, it’s just kind of a bummer because, you know, like I said, you just want something other than like, you know, writing a bunch of tedious wrapper store procedures or writing a bunch of tedious dynamic SQL. And that’s a good example to give you some, like just some break from like these types of problems and queries. And you always hope that like, you know, like things like inline table valued functions, which have so much good use and application and can help with a wide variety of problems caused by other types of functions.
that, you know, like they would just give you some respite from these other things, but they don’t. And this is something that I do have to, you know, explain to clients a bit, which is why I’m talking about it here. But, you know, it’s just kind of a sad face for me.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something, even though, you know, it’s hard to enjoy yourselves when you’re being disappointed. It’s a little difficult to maintain enjoyment when disappointment is up here.
Enjoyment tends to fall off down here. But, yeah, just, you know, it doesn’t work is, I guess, my point in all this. Anyway, I’m going to go hopefully figure out something less disappointing to talk about.
So, anyway, 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.