Difference between temporary table and table variable

Question

Post reply

Difference between temporary table and table variable

deepikamm

SSCommitted

Points: 1681
More actions
January 31, 2011 at 11:15 pm

#234368

I need to know the difference between temp table and table variable..n also the similarities between them..plz help me with it.

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

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 1

Hi

Just refer this post

http://www.sqlservercentral.com/Forums/Topic1055670-391-1.aspx

Just searching the older posts will fetch you answer for these questions.!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

ColdCoffee

SSC-Dedicated

Points: 39972

January 31, 2011 at 11:24 pm

#1280754

Click Here

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 3

Hi ..Another link

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 4

Similarities are listed in this link

http://www.mssqltips.com/tip.asp?tip=1556

deepikamm SSCommitted Points: 1681 More actions · Answer 5

deepikamm

SSCommitted

Points: 1681

February 1, 2011 at 12:17 am

#1280775

Thank you so much guys..it really helped me a lot.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

deepikamm (1/31/2011)
I need to know the difference between temp table and table variable..n also the similarities between them..plz help me with it.

One of the best articles on the subject is right here on this very site. The author's name is Wayne Sheffield. Check it out...

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I recommend to my clients to ONLY use temporary tables unless they have one of the following situations: a) VERY high volume OLTP hits where they need to avoid recompilations to improve performance or b) have massively complex sprocs with repeated DML on temp tables that are causing recompilation issues.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service