table datatype vs temporary table

Question

table datatype vs temporary table

Ananth-431739

Ten Centuries

Points: 1185
More actions
December 14, 2007 at 5:13 am

#127357

Hi,
which is better table datype or temporary table? i'm going to copy and manupulates lakhs(appx.) of records. can i store in table datatype or temporary table?

Viewing 8 posts - 1 through 8 (of 8 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Ananth (12/14/2007)
Hi,
which is better table datype or temporary table? i'm going to copy and manupulates lakhs(appx.) of records. can i store in table datatype or temporary table?

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx contrasts the advantages/disadvantages of table variables and temp tables.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

And another good article I like recommending on temporary tables vs table variables:

http://support.microsoft.com/?kbid=305977

In summary, depends on the size of the table and whether you need indexes.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

David.Poole SSC Guru Points: 76191 More actions · Answer 3

The Microsoft article says that table variables are not memory only structures and are created in TEMPDB if needed. My experience has been that large inserts into table variables i.e. upwards of a million records has caused "out of stack space" errors to be recorded.

For heavy duty inserts/updates I tend to use temporary tables. For short punchy operations I prefer table variables.

If you need to index the data you want to put into temporary storage then you have to use temporary tables as you can't index table variables, other than the primary key.

LinkedIn Profile

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

I just got 2k5 so I haven't done any performance testing (yet) to compare the two... and, I'll probably get all sorts of flack for this... but based on the two articles cited above, the fact that they both live in memory until they get too large, and some personal experiences with trouble-shooting code when "temporary" structures are used, my recommendation is that the ONLY time you should use table variables is in UDF's where temp tables simply will not work or when you're writting some example code for a forum and need a safe place to example data.

From the posts I have seen about the two temporary structures in 2k5, I get the impression that it's a rare thing for a table variable to beat a temp table if the code is written properly with performance in mind.

You can help avoid some of the recompiles by ensuring all the DDL associated with Temp Tables is done before any DML executes, but a recompile is not a bad thing when it comes to batch code... sometimes it's not even a bad thing for GUI code as I've recently seen a lot of posts with the recommendation to include a "WITH RECOMPILE" in the code. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

i'm going to copy and manupulates lakhs(appx.) of records

Oh, yeah... almost forgot... if you're going to do something to "lakhs of records", then forgot about the table variables... they do not and cannot be made to use statistics. Either way, though, you need to make sure that TempDB is large enough... our production system at work preallocates 12Giga-Bytes for TempDB when it boots and it's worth every byte. We've converted many an 8 hour batch job to a ten minute batch job by using Temp Tables to store interim results using "Divide'n'Conquer" methods.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David.Poole SSC Guru Points: 76191 More actions · Answer 6

Jeff, I have found that small amounts of data in table variables outperforms the same thing done in temporary tables.

As data volumes increase table variables rapidly lose any advantage. Determining the precise cross-over point where one starts to outperform the other is difficult as there are so many factors involved.

Data operations in temporary tables are logged but data operations in table variables are not so there is a gain to be had there.

LinkedIn Profile

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

I've found table variables fine for <10 rows of data. Past that the lack of statistics and resulting poor cardinality estimate tends to stuff up the optimiser ten ways from sunday

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass