SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Things You Didn't Know About Temp Tables and Table Variables


Things You Didn't Know About Temp Tables and Table Variables

Author
Message
cy-dba
cy-dba
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2187 Visits: 963
Good article! Although I have not used table variables much in the past, I have started using them more and will keep in mind data integrity issues going forward.
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4925 Visits: 2384
Great article. Short, to the point, and well written.

ATBCharles Kincaid
JJ B
JJ B
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 Visits: 2862
I'd like to second BobAtDBS's question.

For some reason, I find the syntax for table variables to be more attractive. I started using them as soon as they were available to me. Since I rarely use table variables/temp tables and they have so far only been to hold relatively small amounts of data (with no need for constraints, etc.), it doesn't seem to matter which one I use.

While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables? Why did MS add table variables if there aren't at least some circumstances where there is an advantage?

Here's my *guess*: There is a new feature coming out in SQL Server 2008 where you can pass table variables as arguments/parameters to stored procs. Perhaps this only works for table variables and not temp tables?

Even if that's true, it doens't explain why they created table variables for SQL Server 2005 (or was it earlier).
timothyawiseman
timothyawiseman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3784 Visits: 920
blandry (7/17/2008)
Great article, and thanks for it - but under the heading of "the more I learn the less I know"...

Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?


I agree with Blandry, this was a great article.

As to advantages and disadvantages, as many people have said sometimes one is better than the other. The ability to create indexes, add columns and use other "Alter Table" commands on a table variable can be handy in certain occasions.

I personally prefer to use table variables in most situations because they only exist in the context of the currently running script whereas temp tables exist within your session. This can make a big difference about whether or not I need to worry about dropping temp tables and checking for the existence of temp tables if I am debugging and script and therefore running it over and over with small changes.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
JJ B
JJ B
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 Visits: 2862
I personally prefer to use table variables in most situations because...


Oh ya! I remember now. Thanks!!
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5415 Visits: 3069
nice article....2 limitations to add to the list:

1) as "sbateman" mentioned, Table Variables cannot be used in SELECT ... INTO statements

2) Table Variables cannot be used in sub-processes, such as: EXEC, sp_executesql, a Trigger on a table updated by this proc or batch, or sub-Procedure calls. But Temporary Tables are just fine in these situations.


Something to note about performance: I cannot remember where I read this but it was mentioned that Table Variables, not existing as a full table like Temporary Tables do, only ever appear to the Query Optimizier to have one row in them regardless of how many rows really exist. So, as the number of rows increase, the Optimizer is more likely to choose an inappropriate (i.e. inefficient) execution plan--one that is designed for a small set of rows instead of a larger set of rows.

SQL# - https://SQLsharp.com/
Sql Quantum Lift - http://SqlQuantumLift.com/
mojo-168709
mojo-168709
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 831
Thanks for the great article.
Adam Machanic
Adam Machanic
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4797 Visits: 735
One big difference not mentioned in the article, but which influences the choice of when to use which, is that autostats do not apply to table variables, but do apply to temp tables. This means that for temp tables, a lot of time can be spent creating statistics, which may or may not be valuable. On the flip side you save that time with table variables, but the same argument applies -- if they were needed you're in trouble, if not you're in better shape. I tend to favor table variables over temp tables A) for smaller sets of data (2-3 data pages, max), a B) for situations in which I'm defining a PK on the table on doing all joins on that key. In the former situation I don't care as much about plan choice because the table is tiny, and in the latter situation statistics generally won't influence plan choice and so are not as necessary.

--
Adam Machanic
whoisactive
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)

Group: General Forum Members
Points: 383591 Visits: 43058
blandry (7/17/2008)
If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?


Not quite right... Temp Tables and Table Variables both hit memory first.

Although very well written, one of the big things missing in this article is that Table Variables do not and cannot be made to use statistics. Depending on what you're trying to do, that can be a huge disadvantage or a huge advantage.

The other thing about Table Variables is they die as soon as the run stops... that can make them difficult to troubleshoot because you can't see their contents by doing a simple manual SELECT like you can with Temp Tables... it has to be done in the code and you have to rerun to get the Table Variable to populate every time you want to see it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Roi Assa
Roi Assa
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 153
Thank you all for your great feedbacks.
In this article I tried to illuminate things I didn't find in any other article. In order to find common differences between Variable table to Temp table I added in the beginning of this article 2 links:
1. Frequently Asked Questions - SQL Server 2000 - Table Variables
2. Is a Temporary Table Really Necessary?

Is there really any difference or advantage between Temp Tables and Table variables?


When is it advantageous to use a table variable?


While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables?


Yes. there is a different between those 2 in:
- Table variables result in fewer recompilations of a stored procedure as compared to temporary tables
- Table variables do not maintain statistics like temporary tables can (good for large tables)
- Temp table definition can be altered by alter table
- Select into and Insert Exec can be used only with Temp table
- Table variables require less locking and logging resources
- You can't create non-clustered indexes on table variable
More...

As I said, i tried to mention unknown behaviors.
Adam Machanic mention few differences (as I wrote above) and he's absolutely right.
Anyway, you should pick between them respectively and think of what you need before.

I'd like to get feedbacks if you'd like to have an article about all differences between those 2, and give more details then the paragraph above.


Kindest Regards,

Roi Assa

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search