Learn T-SQL With Erik: Inline Table Valued Functions
Video Summary
In this video, I dive into the world of inline table-valued functions in SQL Server and explore why they might be a better choice than scalar or multi-statement table-valued functions for certain scenarios. I explain that these functions are essentially queries wrapped in a function call, much like views but with parameters, which can lead to more efficient execution plans due to their ability to be inlined directly into the query. I also highlight how inline table-valued functions avoid some of the performance pitfalls associated with scalar UDFs and multi-statement TTVFs, such as preventing parallel execution and automatic inlining restrictions. By walking through examples and comparing them with other types of functions, I demonstrate their potential benefits and when they might be most useful for improving query performance.
Full Transcript
I don’t want to alarm any of you, but I have taken 10 milligrams of creatine today and I can now see the future. So what I predict is that we are going to talk about inline table valued functions today. Crazy, huh? Inline table valued functions are, of course, the one kind of function that is really only as bad as the query you put in it and the indexes that you have to support that query. There are many side effects of using scalar user defined functions and multi-statement table valued functions that we talked about over the last couple of days that would hopefully dissuade you from trying to use them in any way where performance matters to your queries. Inline table valued functions really don’t have those side effects. They are just queries. They’re like views that can accept parameters. So that is an important distinction there. You know, you know, you can put, you know, you can put, you know, you can put, you know, you can put a query in there, but what people tend to put in views is a whole other matter. Inline table valued functions share that similarity where, you know, you can, you can put a query in there, but the quality of that query depends on the author. Anyway, we’re going to talk more about that in a moment.
But before we do, we know it’s pause for this commercial break where if you look down in the video description, you will find all sorts of ways that you can give me money and interact with me for free. If you would like to hire me for consulting, great. I’m available. I am a consultant. I consult. There’s probably all sorts of cool Latin words for that that I’m not aware of. You can also buy my training, performance tuning training, or if you wanted to, you could buy the full course. That encapsulates all of the material and more that we have talked about in these YouTube videos. You can also become a subscribed member of the channel where you can give me a little tiny bit of money every month just to support these videos. You can, for free, ask me office hours questions. And of course, if you enjoy this content in any way, shape, or form, you may like, subscribe, and tell not one, but all of your friends at once.
Send out a mass email and say, Hey, everyone. This is the best SQL Server channel on the internet. Wild, right? Anyway, things I am going to be doing outside of the home this year. Well, I gotta go away a little. The nice folks at Redgate were kind enough to say, Hey, Eric. I forget what they said. Anyway, I’ll be in Dallas September 15th and 16th. Utrecht, the Netherlands October 1st and 2nd.
I’ll be in the past data community summit in Seattle, November 17th to 21st, where I will have two joy-filled days of pre-cons with Kendra Little, talking about a lot of T-SQL matters and all the T-SQL that matters. I’m going to work on that pitch line a little bit. Still workshopping things.
Anyway, let’s talk about inline table-valued functions here. We must get to SSMS. So, where scalar UDFs, they would say something like return a data type here, and multi-statement table-valued functions would return a defined table variable here. This just says return table, right? And this is not a physical table.
One thing that’s important to understand about queries in SQL Server is that any query really returns tabular results. So, that’s why when you, say, use a derived table or a CTE or some other kind of table expression, including like a values clause, once you’ve sort of selected from that, you can nest that from in another from, and you can like manipulate the data or filter the data further with further froms and where’s and joins and whatnot.
So, this just returns a tabular result. Of course, saying returns tabular result is a bit too verbose for even Microsoft’s team of developers. So, it just says returns table.
But keep in mind, it’s not a physical table. It’s just a tabular result. And what we can do with these functions is get away with things that we could not maybe get away with if we were to write a scalar UDF. So, if you remember the video from a couple days ago where I talked about scalar UDFs and how things like these non-deterministic built-in functions, like sysdate, time, get date, and that whole family of things, would prevent the automatic inlining of a scalar UDF.
We do not have that restriction when we create an inline table-valued function. Why that is a restriction, I’m not sure. But what I can tell you is that this is perfectly capable of being inlined when it is written as an inline table-valued function that just returns a table.
So, if we look at the estimated plan for this. Now, if you remember, with scalar UDFs and multi-statement table UDFs that return a table variable, when we ran those queries, there was this separate execution plan like down below, right?
So, this is our non-inlinable scalar UDF. If we were to get the estimated execution plan for the inline table-valued function, which is this thing, and we were to say, give me this estimated execution plan, there’s no separate execution plan for this, right?
It’s just the body of the function is inlined into the statement itself. There are, of course, going to be differences here where we turn on actual execution plans and run these. We’re going to see that, you know, this query spent 196 milliseconds scanning this and this.
And, you know, this compute scalar now has some time assigned to it where before it didn’t. But, like, because this was inline, this compute scalar actually, like, ran a function, so there’s time assigned to it. Now, of course, to compare these a little bit further, if we come over here, this top one will, the non-inlined function has this non-parallel plan reason where the inline table-valued function does not have that.
So, inline table-valued functions do not have a couple of the properties that scalar UDFs do, which is the once-per-o execution and the prevention of parallel execution plans by the query that invokes the UDF. So, inline table-valued functions do not have some of the immediate baggage that multi-statement table-valued functions do.
And if we run, well, we already ran that query. Now, another important distinction between the two is when you have an inline table-valued function that touches data, right? So, rather than just, like, let’s just, for the sake of expediency, let’s just call this, what this thing does, processing in memory, right?
Let’s just say that, because you’re not really touching anything physically. You’re not touching any physical objects on disk or in memory, right? I mean, memory isn’t really physical.
I mean, RAM is a physical object, but the stuff in memory isn’t a physical object. But let’s just call this in memory, because all this is doing is accepting some input and doing a little bit of math on it, right? So, this is, like, a lot of just, like, CPU processing.
We don’t have to go out and, like, read data pages from a table or index and, like, bring them into memory and do anything with them there before we start handing them out to queries. This function does, though, right?
This function goes out and touches two tables that live in our database posts and comments here. So, I think I already created this function, but we’ll do it once more for good luck. So, if we look at the scalar UDF from a couple days ago that does the same thing, remember, the estimated plan will show us what happens inside the scalar UDF, but the actual execution plan will not show us what happened inside the scalar UDF, right?
So, we’re running this scalar UDF plan, and it is taking its sweet time. And now we finally have an execution plan, but all we see is that we spent about 200 milliseconds here, but then on this compute scalar, we spent nearly 10 seconds, or a little over 10 seconds, actually, because it’s 10.288 minus 0.190, right?
And, of course, SQL Server remains telling us that we must index the user’s table to make this query faster, which we know is a bunch of hokum, because what happened in here was the bulk of the execution time.
Even though it was 0% of the cost, all the time in the plan was spent here, right? 10-something seconds. But an inline table value function is, of course, a lot more honest with you about what it does.
If we just get the estimated execution plan here, we will see that we did indeed touch the post table, and we did indeed touch the comments table, right? We see all the work involved here.
And if we run this query and get the actual execution plan, we will see, not only is this query a lot faster, granted it was allowed to go parallel, which is part of the help, but even if we ran this at max stop 1, it would be faster than 10 seconds.
But, because it’s allowed to go, well, sorry, if we ran this at max stop 1, it would still be faster than 10 seconds. I’m not sure how much faster.
But we are allowed to get a fully parallel plan here, and we do get a lot more honesty about what the execution plan did along the way, touching all these tables and indexes over to the side here. So, again, inline table valued functions are just like a view that accepts a parameter.
They are really only as terrible as the query that you put in them, and the indexes that you have to support that query. Remember, queries and indexes do tend to go hand in hand in that way.
So, we always do need to consider indexing when we are considering query performance, sometimes more than others. Anyway, that’s about it for inline table valued functions.
I do recommend, if you are in need of encapsulating some code in a functional way, that you do your best to choose these over scalar or multi-statement table valued functions, because they do not have the built-in performance baggage that those two types of functions do.
The inline table valued function does not force your query to run single-threaded or necessarily execute per row. Though, if you get a nested loops join plan, it is sort of per row anyway, but not in the same sort of ugly procedural way.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. This is getting published on a Friday, so I hope you have a great weekend. And, yeah, that’s about it for me.
All right, 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.