The State Of Table Variables In 2025
Video Summary
In this video, I delve into the state of table variables in SQL Server as we approach their 25th anniversary since their introduction in SQL Server 2000. With Microsoft’s recent announcements about the “try us out” program and potential new features for SQL Server 2025, it’s an interesting time to revisit how these variables have evolved. I explore the nuances of table variable cardinality estimation, particularly focusing on SQL Server 2017’s interleaved execution and 2019’s deferred compilation, which offer some improvements but still fall short in providing statistical information about the data within the table variable. By walking through practical examples using SSMS, I demonstrate how these variables behave differently outside of stored procedures versus inside them, highlighting the challenges and potential pitfalls for query optimization.
Full Transcript
Erik Darling here with Darling Data. And again, looking like it’s going to stay that way for a while. My repeated attempts to get acquired for a stack overflow amount of money have gone unanswered. So unfortunately, we’re just going to have to keep doing these videos for a while, I guess. In today’s video, we are going to talk about the state of the table variable. You know, it is the year 2025. SQL Server 2025 is, I guess, sort of around the corner. You know, usually, usually these things happen around the end of the year, but you know, since Microsoft has announced the, like, you know, the, the, the, you can try us out program, you know, we can, we can safely assume that something is in the works. And, you know, people, I, so this is actually going to be interesting. Nobody’s going to talk about it. So, November 30th of this year will be the 25th anniversary of SQL Server 2000. And in SQL Server 2000, table variables were introduced. And so this November will be the 25th anniversary of people being absolutely wrong about table variables. Now, a little bit has changed, right? Back in SQL Server 2017, I believe, think about it.
It was 2017. I forget how far it got backported, but I think it was one of the IQP features from 2017. We got something called interleaved execution for functions that, that, that, for multi-statement table valued functions that, that have, you know, return a table variable, where a SQL Server would no longer just guess one row. It would now guess a thousand rows or something like that. And then SQL Server 2019 introduced another cardinality estimation help, another, maybe it’s a hack. Another part of the thing that’s part of the intelligent query processing family called table variable, table variable deferred compilation, which means that in a normal query, like you were just running a query in SSMS, like, like outside of a module, let’s say, like outside of a store procedure. SQL Server would just figure out how many rows are in the table variable. It would not, like, so like if you put a thousand rows in there, SQL Server would be like, oh, there’s a thousand rows in here.
There, there is still, however, no statistics, even with an index on your table variable, there is still no statistical information about the data, the distribution of data within the, any column on the table, in a table variable. So you’re still missing that. But then the context of a store procedure that changed a little bit more. Because now, instead of just always getting that one row estimate, again, unless you like, you know, throw a recompile hint on there or something.
What SQL Server will do is sort of parameter sniff your table variable, not for any statistical information within the column, but just the number of rows in there. So like, if you put like a one row in a table variable, the first time a store procedure is executed, it’ll still keep guessing one row. But if on that compilation, you put a thousand rows in there, SQL Server will keep guessing a thousand rows.
That could be good or bad if the number of rows in that table variable has a wild swing to it. If like on one row, it’s, sorry, if on one execution, it’s, you know, a thousand rows, the next execution, it’s a hundred thousand rows. Obviously, that’s just another parameter sniffing problem for you to deal with.
So maybe you need that recompile hint anyway. I don’t know. But anyway, we’re going to talk a little bit about some of that stuff in today’s video. So as always, if you would like to support my grand endeavors and designs in producing this content, you can go to the video description and you can sign up for a membership for as low as $4 a month.
If you don’t have four bucks a month, your mom cut your allowance off, I don’t know, something happened, you know, dad lost his job at the mill. So if you want to ask me questions, go to that link and there will be like, you know, some stuff about question asking. Right. So more information there. Good things to know.
If you need help with your SQL Server, if, you know, you’re watching these videos and you’re like, man, Eric maybe seems to know what he’s talking about a little bit there. You would be correct because as Beer Gut Magazine has established many years in a row now, I am the best SQL Server consultant in the world outside of New Zealand. If you need any of help, if you need any help with any of this stuff, I am available and my rates are reasonable.
If you would like some very reasonably priced, very reasonably priced, apparently speaking clearly is not a reasonable offering for me today. But if you would like some very reasonably priced SQL Server training, you can, of course, get all mine. Again, video description has everything you need for about $150 USD when you apply that magnificent discount code right there.
Upcoming events, we, of course, still have SQL Saturday, New York City 2025 coming up on May the 10th at the Microsoft offices in Times Square. So be there or I guess you’re going to be a square either way, aren’t you? All right. Anyway, let’s move on with the show.
We have SSMS open. Let’s make sure our database is in the correct compatibility level to get table variable deferred compilation happening. And we can also see, as far as database scope configurations go, that this deferred compilation underscore TV.
Do not adjust your television set. They couldn’t be bothered to write out table variable or, you know, call this something a little bit more human friendly. But it is on.
By default, it is on. You can see that here. One thing that I am sort of excited about in Management Studio version 21, right? This is SSMS 20. Management Studio 21 has all of the data, has like a GUI page for all of the database scope configurations.
So, you know, I guess it is about time there. So let’s make sure that we have the right index in place. And while we do that, I’m going to walk through a little bit of what’s going on here.
Now, I’ve got this query with, I mean, there’s a little bit of sort of superfluous stuff in there that I use to make my life a little bit easier down below, which I’ll show you in a moment. But these are the top six parent IDs in the post table, right? So you can see parent ID zero.
Now, again, this is a normalization problem in Stack Overflow. They don’t have a questions and answers. Rather, they don’t have separate tables for questions and answers. They only have posts.
And posts is all the questions and all the answers. And you have this parent ID column that tracks which question answers belong to. So if you have a parent ID of zero, you are a question.
If you have a non-zero parent ID, you are an answer or some other type of thing that’s tied to a question. And, you know, there’s a pretty steep fall off here, right? So, like, there are six million questions and, well, six million fifty thousand eight hundred and twenty questions in the post table.
And then a few of the posts have a sort of high number of answers. What kind of post would generate five hundred and eighteen answers? I don’t know.
I mean, on the database administrator stack exchange, you’re lucky to get one answer that’s an answer. Well, I guess not lucky, but getting two good answers is, you know, extremely rare. But on Stack Overflow, actually, I’m not saying that any of these answers beyond one of them is good.
I’m not even saying that the answer that’s currently marked as the answer is even still correct. Who knows? This might be 20 years old. And, well, I guess 2008 can be 20 years old, almost 20 years old.
And it could be woefully out of date at this point. It could be all sorts of wrong. But this is this is what we’ve got, right?
Just none of that really makes a difference here. All we care about is that there are some numbers. So let’s outside of a store outside of a module like a store procedure. We’re going to look at what table variables do.
And I have a recompile hint here because I just I just want you to see that the recompile hint doesn’t help us statistically in any way. But I’ve got a table variable up here that has a clustered primary key on it. Right. So I’m creating an index on the table variable because I want you to understand that SQL Server does not have any statistical information about what’s in the table variable no matter what.
So but what what I what I thought was sort of interesting is if we run this query passing in just one parent ID. Right. And I’m going to save the zero one for later because that’s when things get sort of interesting. Like this is like this is a good enough query slash query plan.
You know, SQL Server like like one row is coming out of here. Yes, that’s actually correct for this. We only we did only put one row in the table variable. But then SQL Server guesses three rows are going to come out of this join to posts.
In reality, we get 518 rows. If we were using a temp table with no pound sign temp table where I’m sorry, pound sign temp table or however they do it, then SQL Server would be able to generate statistics, would be able to do better cardinality estimation for the join.
But what caught my eye a little bit was that every time you add a row to the table variable and this is I think I want to say this is only true for the new the default, the newer cardinality estimator. I want to say default because I hate I hate calling that thing the default.
But I want to say this is only for goes for this where they do the course join alignment. It could be true for the legacy one. I didn’t get around to testing that.
But for every row you add in there, SQL Server basically guesses that three more rows are going to match here. So now instead of one row, we get three rows. And instead of SQL Server guessing that we were going to get three rows as a result of the join, now we get nine. And if we put in the last two, the last two rows for this set that we might care about, you’ll see that now the SQL Server is going to guess 15 rows.
Right. So for for when we put five rows in there, SQL Server is like, oh, well, I think 15 are going to come out. Right. So we get that number down here. So this this still isn’t a very good guess.
Right. Because, you know, like, granted, this is a pretty small number of matches. So like this kind of being off by this, like even though it’s like 11,700 percent off, like the index is good enough and the query plan is good enough that like it’s not a big difference for this. Where that gets real messed up, though, is if we change this, of course.
Right. Because if we like this is like even though these these questions have the most answers as the current state of the 2013 SAC overflow database, like that’s still not that many. Right. We still get like like seventeen hundred and fifty five rows for all of them, which isn’t a ton. Right. So guessing 15 and getting 17, 55 is far from the end of the world.
Right. You’re not going to you’re not you’re not going to get the worst query plan in the world for that. Right. So let’s look at this now, though, with one row, but that one row is zero. So this is where things do start to make a difference, because now we have we put one row in the table variable, but SQL Server doesn’t know what that one row is.
So SQL Server is going to guess three rows again. But that’s that’s a way off guess. Right. So SQL Server is like, oh, well, I think one row is going to I think one row is going to come out of here, which it’s right about.
It just doesn’t know what the value for that one row is. So we can’t look at the statistics that are available on the post table and say, hey, how is the value coming out of this thing going to match up with the values down in this thing? Right. So SQL Server knows one row is coming out of here.
The one row estimate is correct, but it doesn’t know what the value of that one row is. So when we come in, when SQL Server is doing its initial compilation of the query and it’s like, well, how many rows are going to match in here? It has no way of like looking at the histogram in here and saying, OK, like I have a histogram value either for zero or that is like has zero in its range.
I can make a better guess about how many rows I think are going to come out of this. So it chooses the same query plan as it did for the smaller number of rows and things just get all mucked in here. Right. So this is a terrible this is a terrible execution plan for this for for the value of zero.
But because SQL Server doesn’t have anything statistically to line this up with this, but just like we just can’t we just can’t. There’s really no better way of modeling this sort of thing. There’s no better assumption you could make.
Right. So that’s it. That’s like keep in mind this is, you know, with a with a recompile hint and with the cluster primary key on this thing. So SQL Server can’t make a very good guess here at all. Right. This is lost in the woods.
Now, like and like like I said, that does change a little bit in the context of stored procedures. Right. And I’m taking recompile hint out of the equation here because I want to show you that the number of rows that the store procedure compiles within a table variable persists from one execution to the other.
So what I’ve done is I’ve created a table type called pattern ID so that I can pass in a table valued parameter here. Now, table valued parameters do behave somewhat differently than table variables. They actually had this behavior before.
So like if I so like even so like if I declared a table variable inside of the store procedure and dump this table variable into that table variable, the same thing would happen. Right. So I just want you to want you to understand that a little bit.
But I think I just did this correctly. We’ll see that table type is already created. I’ve already I’ve already run through and tested this. So let’s do this once.
Let’s declare we have to declare our table variable. We have to map it to our table type. Right. That’s what we’re doing up here. Then we’re going to insert our five row matches, five rows of matches into the table variable. And then we’re going to execute the store procedure passing in the table value parameter to it.
So when we do this SQL Server in the execution plan here is like, ah, there are five rows in you. Wonderful. We’ve got you’ve got five rows.
We’ve got that all figured out. Right. Cool. But now when we run this for zero, you know, we’re still we’re going to get the bad query plan again, because even in a store procedure where SQL Server, you know, uses the table value parameter, which is backed by a table variable, we are unable to get good cardinality.
Now, I think this is especially important because let’s just look at the query plan real quick. We get that same thing. This all takes about 10 seconds. SQL Server, again, it cached the query plan where it thought five where we had five rows initially.
But now we get we only have the one row in there. But SQL Server still doesn’t know what that one row is. Right. So even in the context of a store procedure, table variables are still pretty messed up. Now, where this, I think, gets important is sometimes people think that the table value parameter situation is different because they’re backed by a table type.
So notice when I created this type here, parent, I created a type called parent IDs as a table. People sometimes get this confused with this and think that this is creating a like a real backing table for the table value parameter. But it’s not. Right.
So even though we have a clustered primary key on this thing, SQL Server is not generating statistics on this either. Right. There’s no statistical information about this because we’re using the table variable to hold this or to sort of be assigned that type. Right. So the table variable here is really where you run into the issue because SQL Server is still not giving any love to table variables, histogramically, statistically, whatever you want to call it.
So like even table value parameters are backed by a table type. They still don’t get statistics. SQL Server 2019 deferred table variable compilation only gives you the table cardinality, the number of rows in the table, not the contents of the columns. Right. So still no histogram, still no table level distribution stats.
And you can still run into all of the same problems with table variables if you have skewed data or rather you join to that table variable in some way. Right. It could be a join, some query exists, whatever. You know, however you want to correlate to the to the table variable, you still run into the same problem where SQL Server doesn’t is unaware of the contents of the table variable.
And the cardinality estimates that you get are not going to reflect the sort of reality of what you’ve done in there. So anyway, that’s my spiel. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I do hope that you will stick around and try to learn some more SQL Server stuff from me. I do enjoy I do enjoy talking about it, don’t I? You can tell because I just went on for about 18 minutes about table variables again.
But anyway, what can you do? Some some horses deserve to be fed. And because, you know, I still have to help clients with this table variable stuff, I feel like I need to keep talking to the greater world about this table variable stuff because God almighty, no one seems to be learning their lesson.
So anyway, I’m out of here. 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.