Temporary tables and Table variables storage

Question

Post reply

Temporary tables and Table variables storage

sergey.gigoyan

Ten Centuries

Points: 1213
More actions
August 31, 2016 at 11:01 pm

#316852

Comments posted to this topic are about the item Temporary tables and Table variables storage
August 31, 2016 at 11:07 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

September 1, 2016 at 1:05 am

#1898690

Nice and easy question.

Derek Slinn SSC Enthusiast Points: 142 More actions · Answer 2

I think the answer is wrong,

Both are stored in tempdb database.

technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 3

Derek Slinn (9/1/2016)
I think the answer is wrong,
Both are stored in tempdb database.
technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger

From BOL

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
- Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

A nice and simple question that will likely spark some debate. At this time, only 54% right with 256 respondents.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Wow, got that one wrong. I thought the table variables were in memory. Learned something today.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

Derek Slinn (9/1/2016)
I think the answer is wrong,
Both are stored in tempdb database.
technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger

That's what I thought.

Mike Hays SSCommitted Points: 1871 More actions · Answer 7

Good Question, I almost tripped up on that one, attempted to over think it...

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Iwas Bornready (9/1/2016)
Derek Slinn (9/1/2016)
I think the answer is wrong,
Both are stored in tempdb database.
technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger
That's what I thought.

I would say it depends. The data for both starts on RAM and when they grow it's thrown to disk.

However, their definitions are stored on tempdb since the beginning. Table variables are stored as a temp table with an eight character name preceded by #.

DECLARE @Date datetime = DATEADD( ms, -10, GETDATE());

DECLARE @MyTable TABLE(someid int);

SELECT *

FROM tempdb.sys.tables

WHERE create_date BETWEEN @Date AND DATEADD( ms, 20, GETDATE());

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

Check Martin Smith's excellent answer at StackExchange about temporary tables vs table variables, particularly the bit about storage location: http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

Cheers!

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 10

Derek Slinn (9/1/2016)
I think the answer is wrong,
Both are stored in tempdb database.
technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger

Yep that's what I thought temp tables will always be created in the tempdb, table variables can flush to the tempdb if they need to but only if they need to.

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Revenant

SSC-Forever

Points: 42467

September 1, 2016 at 11:34 am

#1898840

Interesting discussion. Thanks!

George Vobr SSCrazy Eights Points: 9916 More actions · Answer 12

Mike Hays (9/1/2016)
Good Question, I almost tripped up on that one, attempted to over think it...

+1;-)

Interesting question, thanks Sergey 🙂

MMartin1 One Orange Chip Points: 27768 More actions · Answer 13

SQL Server could hold everything in RAM. Yet the database objects need to be tagged as to what database they belong to. I look at it as temp tables and tables variables being borrowed from the temp database.

----------------------------------------------------