Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Things You Didn't Know About Temp Tables and Table Variables Expand / Collapse
Author
Message
Posted Thursday, July 17, 2008 7:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, 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.
Post #535961
Posted Thursday, July 17, 2008 7:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Great article. Short, to the point, and well written.

ATB

Charles Kincaid

Post #535990
Posted Thursday, July 17, 2008 9:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:01 PM
Points: 266, Visits: 2,567
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).
Post #536089
Posted Thursday, July 17, 2008 9:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #536108
Posted Thursday, July 17, 2008 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:01 PM
Points: 266, Visits: 2,567
I personally prefer to use table variables in most situations because...


Oh ya! I remember now. Thanks!!
Post #536119
Posted Thursday, July 17, 2008 9:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:58 AM
Points: 354, Visits: 1,869
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# - http://www.SQLsharp.com/
Post #536123
Posted Thursday, July 17, 2008 10:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, Visits: 831
Thanks for the great article.
Post #536137
Posted Thursday, July 17, 2008 1:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #536267
Posted Thursday, July 17, 2008 2:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:18 PM
Points: 36,772, Visits: 31,228
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #536306
Posted Thursday, July 17, 2008 3:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:19 AM
Points: 29, Visits: 149
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

Post #536355
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse