What are table variables in sql server and why they are used?

Tech Novice
Tech Novice used Ask the Experts™
on
What are table variables in sql server and why they are used?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer

Commented:
A table variable is just like any other variable in that it is declared and stored in memory. Once declared you can use them the same way you would use any other table. You can insert, update, delete, sort, filter, group, anything else you like. Like any other variable, they will be destroyed as soon as the variable declaration goes out of scope.

For relatively small sets of working data, table variables can offer significant performance improvements over temp tables since they are stored only in memory and not written to tempdb at all.

Author

Commented:
very well explained,just wanted to know,is there any rough idea on the count of rows (max rows with optimal performance in table variable)?
Russ SuterSenior Software Developer
Commented:
There are so many factors that come in to play answering that question.

the amount of available RAM on your server
the speed of your storage medium
the fragmentation level of your transaction log
the width of the table(s) you're working with
so many other things

I'd say your performance improvements are still visible within a few hundred rows if the table isn't too wide.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Nakul VachhrajaniTechnical Architect, Capgemini India
Commented:
I ran into the same dilema a few years ago and wrote about it: https://nakulvachhrajani.com/2014/01/13/0318-sql-server-performance-tuning-use-temp-tables-instead-of-table-variables-when-working-with-large-data-sets/

The crux of the post is that table variables are variables. Variables hold a single value. Hence, when you look at the execution plan you can see that SQL Server expects the table variable to contain only one row. When the table variable has thousands of rows, the plan becomes sub-optimal and hence you run into performance problems.

In my personal experience, table variables work best if you will be dealing with a handful to less than 1000 rows.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
For relatively small sets of working data, table variables can offer significant performance improvements over temp tables since they are stored only in memory and not written to tempdb at all.

Just not true.  Table variables are processed exactly like temp tables.

As they function now, table variables should really only ever be used for (1) a single row table(*) (2) to keep error data and other data that you don't want to lose if you have to do a ROLLBACK, because table variables are not rolled back but keep their rows.

(*) The optimizer always assumes table variables store only a single row.  I've seen huge performance issues when they have more, because SQL generates the wrong type of plan.
Nakul VachhrajaniTechnical Architect, Capgemini India
Commented:
Fully agree with Scott.
As highlighted in my post, the reason table variables don't scale up is because they are variables and do not have any statistics. SQL Server assumes that they would only have one value/record and hence builds the plan accordingly. If we end up with a decent sized dataset (just a few thousand records), it can result in the plan being sub-optimal negatively affecting performance.
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
And Yes, they are definitely stored in the tempdb.
Russ SuterSenior Software Developer
Commented:
Just not true.  Table variables are processed exactly like temp tables.
Except that they live in memory and not in tempdb. If your storage media is slow and/or your server has RAM to spare this can easily outweigh performance losses from indexing.

If we end up with a decent sized dataset (just a few thousand records), it can result in the plan being sub-optimal negatively affecting performance.
Exactly why I said you should keep the rowset small (under 1000 rows).

the reason table variables don't scale up is because they are variables and do not have any statistics.
What if indexing is not a factor? What if you're building a result set and only inserting data?

Ultimately, performance is affected on a case by case basis. For sufficiently small datasets there may not even be a measurable difference between indexed table and memory table performance. It's been my experience that for very small sets of data (greater than 1 row but smaller than 1000) I generally see measurable performance improvements using table variables over temp tables. If performance is your main priority, you should test your queries using temp tables and table variables.

I should clarify that I'm not advocating using table variables over temp tables. In fact, when I write queries I try to avoid both. Sometimes, due to complexity or other constraints, they're just necessary.

If I'm writing a function that returns a table I'll probably use a table variable to build the result set since indexing won't be an issue and, in fact, will likely slow down overall performance due to the fact that I'm building (inserting) the table. If I'm writing something that needs to store values early in a stored procedure for later retrieval then a temp table tends to be more appropriate.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Just not true.  Table variables are processed exactly like temp tables.
Except that they live in memory and not in tempdb.

That's just a myth.  Someone said it and many people believed it without verifying it.

MS docs say to limit table variables to 100 rows; I say I've seen big performance issues with less than that.  If you can't live with one, limit it to 5.

This article demonstrates that table variables and temp tables are handled internally the same way by SQL:
https://sqlperformance.com/2017/04/performance-myths/table-variables-in-memory

Or, ask yourself this: If you inserted 1B rows into a table variable, do you think they could all stay in memory??
Russ SuterSenior Software Developer

Commented:
Well, I've learned something new today then. Fortunately, I don't have enough cases where I use either temp tables or table variables in my queries, stored procedures, functions, etc...

I can say that I've definitely seen performance improvements on some occasions but it's been a while and the circumstances may have other factors. I do know, and your article confirms this, that in user defined functions a table variable is your only option.

Author

Commented:
Thanks all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial