Temp Table Vs table variable

Question

Temp Table Vs table variable

Jules Bonnot

SSCertifiable

Points: 5488
More actions
June 7, 2006 at 11:10 am

#117927

Not seen this difference documented..
Code below illustrates one other difference between temp tables and table variables. That that dynamic sql can see temp tables created by calling script but cant see table variables. Presumably this reveals some substantive difference between the way they are treated behind the scenes.
-----Temp Table
create table #test (id int identity, test nvarchar(20))
declare @sql nvarchar(4000)
select @sql ='insert #test (test) select ''ohdear'''
print @sql
exec sp_executesql @sql
select * from #test
------Table variable
declare @tab table (id int identity, test nvarchar(20))
declare @sql2 nvarchar(4000)
select @sql2 ='insert @tab (test) select ''ohdear'''
print @sql2
exec sp_executesql @sql2
select * from @tab
Anyone know why this is and if i could use a table variable here?
www.sql-library.com[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

A variable is constrained by scope. It doesn't matter if it's a simple int or varchar, or a table variable, regardless, it only has scope to the batch containing it.

------Integer variable

declare @test-2 int

Select @test-2 = 123

print @test-2

declare @sql2 nvarchar(4000)

select @sql2 ='print @test-2'

print @sql2

exec sp_executesql @sql2

A temp table is a DB object in tempdb, much different than a locally scoped variable.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

See these articles for more information....

http://www.sqlservercentral.com/columnists/RDyess/temptabl.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

Yep Thanks. of course the hint is in the name 'Tabke VARIABLE'.

WQas reading the thread in responce to the recent article on temp tables where the general concensus is that they arent that different.

Scope is certainly one crucial difference which you would have thought had implications for performance.

www.sql-library.com[/url]

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

>>Scope is certainly one crucial difference which you would have thought had implications for performance.

Why would scope impact performance ? Consider #Temp tables versus ##Temp tables. Again, difference is only scope, but no correlation to performance.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

'A temp table is a DB object in tempdb, much different than a locally scoped variable'

If enough data goes into a table variable it will create an object in tempdb. So it is not clear that local scope and creation of objects in tempdb are mutally exclusive as your post appears to claim.

But of course as demostrated by ## and # table there isnt a theoretical reason why scope shoud effect performance but perhaps the way SQL implents objects of different scope is distinct enough to create performance differences.

There seems to be no concensus on the facts of how TVs and TTs are implemented and a better understanding of the relation of scope to tempdb may shed some light on this.

www.sql-library.com[/url]

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

Both Temp Tables and Table Variables start out in memory and both spill into TempDB when they get big enough... there are many other differences, as well...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

...draw your own conclusions on what turns out to be a fairly controversial subject...

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

Ian Yates SSCoach Points: 19738 More actions · Answer 7

The more tightly defined scope allows SQL to possibly make better judgements about the state of the table - hence less recompiles in stored procs. Table variables, being tightly scoped, are not available universally to the connection. Temp tables have then connection as their scope - thus you can have a number of chained stored procs use them, or even multiple calls from your app (within the one connection of course).

Pros and cons either way - sometimes you may need to create additional indices on your temp table... This cannot be done with a table variable. I tend to use table variables and resort to temp tables when I need to allow an extra index due to some filtering, ordering or grouping, or when it needs to be accessed by another stored proc (this is usually rare but does happen). Finally, if you have a recursive proc that needs to accumulate results then a #temp table can be handy but you can usually unwind the recursion and turn it into a loop and stack anyway.

But Jeff's correct - both start out in memory and spill into TempDB.

Tyson L. Price Mr or Mrs. 500 Points: 589 More actions · Answer 8

I didn't go through the links but another advantage of the table variable is there is no logging so you don't have that overhead.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Does anyone have a Microsoft link or know the lookup in BOL that says that Temp Tables are logged (or not?). I know there is a log file associated with TempDB but I've never seen it grow to hellashish proportions even with very large Temp Tables?

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Jeff

This (and other similar threads) seems to suggest that there is logging in tempdb. Don't know whether there's anything from Microsoft confirming this.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=13898&p=2

John

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Thanks for looking, John. If I find anything from Microsoft, I'll be sure to post 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.

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)