0

I am new to coding in SQL Server stored procedures. I am having difficulty deciding between temp table, table variable, global temp table to use to store data in my stored procedure.

My situation is that I need to store data locally in a table to calculate values while retrieving data from various SQL Servers. Common sense tells me to use temp table, but why can't I use table variable or global temp table?

Any insights will be helpful.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohan RK
  • 33
  • 1
  • 1
  • 6
  • possible duplicate of [What's the difference between a temp table and table variable in SQL Server?](http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server). Also a good read [`Difference Between Temporary Table and Table Variable in Sql Server`](http://sqlhints.com/tag/temporary-table-vs-table-variable/) – M.Ali Aug 05 '14 at 23:01
  • In addition to what these fine people said, you need to think about performance too. Each of these will perform differently depending on your data (size and type). Some allow indexes and some don't. Some use more IO than others. You need to play with them to see what works best for your situation. Then re-evaluate it on occasion. – Steve Aug 05 '14 at 23:03
  • Please try to read this article to complete info: http://sqlhints.com/2014/02/10/difference-between-temporary-table-and-table-variable-in-sql-server/ – zhongxiao37 Aug 06 '14 at 04:36

3 Answers3

0

The rule is pretty much, choose a table variable as the default. Then re-assess the situation in the following circumstances:

If you want to use transaction rollback If you want to pass the resultsets of your tables from one stored procedure to another If you want the query optimizer to be able to work out how best to run a complex query If you’re getting really clever with dynamic SQL, you need a temporary table.

So, table variables ought to be your first choice. For small result sets and the everyday type of data manipulation, they are faster and more flexible than temporary tables, and the server cleans them up when you’ve finished. Temp variable does not persist in tempdb unlike temp table, it will be cleared automatically immediately after SP or function. Temp variable can only have 1 index i.e primary, TT can have more indexes. Use temp variables for small volume of data and vice versa for TT. No data logging and data rollback in variable but for TT it’s available. Less SP recompilations with variables than TT.

Global temp tables are just like temp tables except that they are visible to all sessions as long creating session is valid. Hope this gives you good idea which way to go given your scenario.

Coder0997
  • 232
  • 2
  • 3
  • 14
  • Additional warning on table variables; large amounts of data will truncate without error. IE, you add 50K records but the table only has 40K. The amount it hold appears to be related to your free memory. – Steve Aug 05 '14 at 22:59
  • Sorry: table variables **ARE** stored in `TempDB` - just like temp tables .... [see e.g. this blog post here or many more on the topic](http://www.scarydba.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/) – marc_s Aug 06 '14 at 05:07
  • Table variables do have logging as well. – Martin Smith Aug 06 '14 at 06:09
0

A Table Variable is functionally almost identical to a temp table -- in fact, SQL server actually implements a temp variable as a temp table at least some of the time. There are some functional differences in terms of limitations on what you can do with them that make one more convenient than the other on some occasions, insert the result of an exec is a common on as is using create index to make secondary keys.

A temp var is not guaranteed to be written to tempdb. In fact the Microsoft documentation seems to suggest that it will not be written to tempdb, so in theory, temp vars can be expected to be more performant or at least not slower that temp tables.

A global temp table is a temp table that can be shared on multiple database connections. These are comparatively rare compared to temp vars and temp tables.

ADDED

The differences have been discussed at length in a number of articles article#1, article#2, article#3 and on Stack Overflow

ADDED

From some of the the comment on this answer. I've read those articles in the past, and yes, to the best of my knowledge table vars are always implemented via tempdb today. Since Microsoft could change the implementation in the future or even in 2014 version (since those articles predate 2014). That's why I used the hedge words like "in theory". The Stack Overflow article I referenced mentioned this briefly. But the additional articles references are also quite good.

I was trying to explain that they were basically the same. You can choose based on how you need to use them. And if either one could be used equally, you might prefer a temp var since MS could choose to make this more efficient in the future. Seemed like an awful lot to dump on a newbie question though.

Community
  • 1
  • 1
Gary Walker
  • 8,831
  • 3
  • 19
  • 41
  • Sorry: table variables **ARE** stored in `TempDB` - just like temp tables .... [see e.g. this blog post here or many more on the topic](http://www.scarydba.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/) – marc_s Aug 06 '14 at 05:09
  • By microsoft documentation I assume you mean [this](http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx). Unfortunately it contains a number of inaccuracies and has probably greatly contributed to the confusion on the topic. They are always created in `tempdb` (except those created as in memory OLTP types in 2014). I believe my answer here http://dba.stackexchange.com/a/16386/3690 to be one of the more accurate and comprehensive coverages of the topic. – Martin Smith Aug 06 '14 at 06:16
0

Temporary table is scoped to a session

  Create TABLE #person        ----Statement 1
    ( 
    firstname varchar(100),
    lastname varchar(100)
    );
    INSERT #person   -----Statement 2
    SELECT TOP(250) p.firstname, p.lastname
    FROM person.person p;

    SELECT count(*) FROM #person; -----Statement 3

Whether each statement is run individually or as a batch, #person gets populated
whereas
Table variable is scoped to a batch 

DECLARE @person TABLE        ----Statement 1
( 
firstname varchar(100),
lastname varchar(100)
)
INSERT @person   -----Statement 2
SELECT TOP(250) p.firstname, p.lastname
FROM person.person p

SELECT count(*) FROM @person  ----Statement 3

Unless run all three statements are run as a batch, error message is returned

Must declare the table variable "@person".

Example:- If Statement 2 is run followed Statement 1 or Statement 1 and Statement 2 are run as a batch followed by Statement 3, will give error above.

Rock
  • 1
  • 1