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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roi Assa » Things You Didn't Know About Temp Tables and...

42 posts, Page 2 of 5

««1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

cy-dba

Posted Thursday, July 17, 2008 7:30 AM

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

Charles Kincaid

Posted Thursday, July 17, 2008 7:50 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

Great article. Short, to the point, and well written.

ATB

Charles Kincaid

Post #535990

JJ B

Posted Thursday, July 17, 2008 9:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 259, Visits: 2,424

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

timothyawiseman

Posted Thursday, July 17, 2008 9:31 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 748, Visits: 905

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

JJ B

Posted Thursday, July 17, 2008 9:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 259, Visits: 2,424

I personally prefer to use table variables in most situations because...

Oh ya! I remember now. Thanks!!

Post #536119

Solomon Rutzky

Posted Thursday, July 17, 2008 9:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:16 PM
Points: 285, Visits: 1,386

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

mojo-168709

Posted Thursday, July 17, 2008 10:06 AM

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

Adam Machanic

Posted Thursday, July 17, 2008 1:04 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:47 AM
Points: 1,137, Visits: 676

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

Jeff Moden

Posted Thursday, July 17, 2008 2:15 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #536306

Roi Assa

Posted Thursday, July 17, 2008 3:29 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:56 AM
Points: 29, Visits: 119

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 »

42 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions