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

What are table variables in sql server and why they are used?
Tech NoviceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
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.
0
Tech NoviceAuthor 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)?
0
Russ SuterCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
1
Scott PletcherSenior DBACommented:
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.
1
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
And Yes, they are definitely stored in the tempdb.
0
Russ SuterCommented:
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.
0
Scott PletcherSenior DBACommented:
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??
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russ SuterCommented:
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.
0
Tech NoviceAuthor Commented:
Thanks all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.