Where is my declared table stored?

Question

Where is my declared table stored?

float

Grasshopper

Points: 19
More actions
June 16, 2010 at 12:17 am

#222202

Hello,
in a sql-statement, i declare a table in the currently used database called "geo" ("DECLARE @tablename TABLE .....").
I know that its stored only till the connection is closed.
But can somebody tell me where my temporary declared table is stored physically? (ram or harddisk?)
Best Regards,
float

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

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

sachnam SSCrazy Points: 2317 More actions · Answer 1

Hi,

You have created a Table variable which is always tempary in nature all TEmp tables and variables stay in Tempdb database and all are sessional in nature. i mean to say all these temp variables will automatically flushed out once your session is closed.

temprary variables like in your case pyhsically remains in RAM.

Temp table or Hash(#) tables are kept in the data file of a Tempdb database till the session is alive.

Too many Temp tables might increase you Tempdb size as well so set an adequate size for tempdb database.

I hope you got your answer.

Regards,

Sachin Sharma

float Grasshopper Points: 19 More actions · Answer 2

float

Grasshopper

Points: 19

June 16, 2010 at 1:14 am

#1180790

Thanks 🙂

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 3

sachnam (6/16/2010)
temprary variables like in your case pyhsically remains in RAM.

Myth.

http://scarydba.wordpress.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/

Clear Sky SQL
My Blog[/url]

float Grasshopper Points: 19 More actions · Answer 4

"Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size."

So, if the size of these temp table is small, it will be stored in memory.

http://it.toolbox.com/blogs/programming-life/temp-tables-and-table-variables-when-to-use-what-and-why-part-2-16303

If there is enough memory free, it will definitly be stored in memory.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

sachnam (6/16/2010)
temprary variables like in your case pyhsically remains in RAM.
Temp table or Hash(#) tables are kept in the data file of a Tempdb database till the session is alive.

Common myth, totally and completely wrong.

Both temp tables and table variables are allocated space in TempDB. They are both preferentially kept in memory and only spilt to disk in case of memory pressure. It is completely wrong to say that table variables are in memory only and temp tables only on disk. They are treated the same with regards to their storage.

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

WayneS SSC Guru Points: 95405 More actions · Answer 6

GilaMonster (6/16/2010)
Both temp tables and table variables are allocated space in TempDB. They are both preferentially kept in memory and only spilt to disk in case of memory pressure. It is completely wrong to say that table variables are in memory only and temp tables only on disk. They are treated the same with regards to their storage.

And this physical presence in tempdb can be proved. See this article[/url] which shows how to demonstrate this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BrainDonor SSCoach Points: 19243 More actions · Answer 7

GilaMonster (6/16/2010)
sachnam (6/16/2010)
temprary variables like in your case pyhsically remains in RAM.
Temp table or Hash(#) tables are kept in the data file of a Tempdb database till the session is alive.
Common myth, totally and completely wrong.

Interestingly, while working through Microsoft's training book for 70-433 I came across this, in answer to one of the questions:

"You can eliminate all the intermediate temporary tables by using derived tables, which can take advantages of the memory available on the machine instead of requiring physical reads and writes to disk."

From SSC I've known the truth of it for a while, but when MS's own material implies otherwise it's no wonder so many think that way.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 8

Derived tables are not temp table (either # or @)

What, i think, that question is doing is drawing a distinction between...

create table #table

(Cola int)

insert into #table

Select cola from tablea

select * from tableb

join #tablea

on tableb.col = tablea.col

and

select * from tableb

join (Select col from tablea) as derived

on Derived.col = tableb.col

Clear Sky SQL
My Blog[/url]

BrainDonor SSCoach Points: 19243 More actions · Answer 9

Dave Ballantyne (6/18/2010)
Derived tables are not temp table (either # or @)

I realise that, but it was bit in italics that I found confusing. It implies that temporary tables always requires disk activity.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 10

I see what you are saying, IMO, for the sake of brevity the question is fine.

It would be a much larger book if every question had a footnote for all the "it depends" option 😀

Clear Sky SQL
My Blog[/url]

BrainDonor SSCoach Points: 19243 More actions · Answer 11

Yeah, I'm hoping the multiple-choice questions have 'it depends' as an option - I'll sail through it!

BrainDonor.

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

BrainDonor (6/18/2010)
Interestingly, while working through Microsoft's training book for 70-433 I came across this, in answer to one of the questions:
"You can eliminate all the intermediate temporary tables by using derived tables, which can take advantages of the memory available on the machine instead of requiring physical reads and writes to disk."
From SSC I've known the truth of it for a while, but when MS's own material implies otherwise it's no wonder so many think that way.

Derived tables aren't tables at all.

The quality of the exam guide books is occasionally on the questionable side...

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