Temp tables vs table variables vs derived tables

Question

Temp tables vs table variables vs derived tables

jthart

SSC Veteran

Points: 280
More actions
June 4, 2004 at 12:21 am

#162603

Trying to get the last word on the above three methods considering performance/development and where any one of the three could be used.

In the debate between temp (tempdb) tables and table variables most documentation I've seen prefers the use of table variables over temp tables, except in cases where there is a large quantity of data (where an index may be required). I have heard the view though that table variables suffer with relation to not maintaining statistics and issues with parallel query plans.

With the comparison between temp tables and derived tables again I see the preference is to derived tables. I would guess, since the derived table is part of the actual query, statistics for derived tables would be maintained. If this is the case you would assume that derived tables in some circumstances would be faster than table variables?

As for comparison between table variables and derived tables, I've hardly found any comparisons - maybe because they are not directly "swappable" in 100% of cases?

From a development point of view, to replace temp tables with either a table variable or a derived table (where either could be used) it appears to me the table variable seems a better choice - you can pretty much just replace a temp table name with the table variable name. Plus, as with temp tables, they seem easier to debug because once they are created you can just enter simple select statements within the procedure to see what the data is doing.

Interested in some definitive feedback.

tia

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 75991 More actions · Answer 1

From my own experience, if you have very large tables then rather than joining them together for a query you would do use temporary tables to store the results of your selects and then join the much smaller resulting temp tables.

If you have a medium sized database then I would prefer derived queries.

Table variables seem to get stored in memory, where are temporary tables get stored on the disk within TEMPDB.

It is worth comparing the execution plans between temp tables and table variables when carrying out SELECT, INSERT, DELETE , UPDATE actions. I seem to remember that INSERTs and SELECT are very fast on table variables but UPDATEs and DELETEs were more expensive. Don't quote me on that.

Another factor is the structure of the table. If it is very simple then I would lean towards table variables and conversely, if it was a larger structure I would lean towards temporary tables.

There isn't really a hard and fast rule.

LinkedIn Profile

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

I have ran into this debate as well. In my experience (as well as David's) the only way to get to which is better is to build it and see.

Table variables I have used to hold very small static data for "lookup" only. I have not had good luck with them other than that.

Temp tables I use extensively to pass data back and forth when indexes are required and/or a lot of crunching

Derived tables I also use to help limit the amount of data I am processing.

I prefer the last two options. I have heard that table variables load into memory but honestly again haven't had much luck. Hopefully in the next release of SQL they will work better and become a better tool.

Good Luck,

Good Hunting!

AJ Ahrens

webmaster@kritter.net

mramir74 Ten Centuries Points: 1071 More actions · Answer 3

I agree, I think looking at the overall purpose of the query, execution plan and IO statistics will truly tell what method is optimal. Each method is good depends on your purpose.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. It also prevents the engine to run the query in parallel when table variable is in the query.

See KB 305977 for more information.