Temporary tables and Table variables storage

  • sergey.gigoyan

    Ten Centuries

    Points: 1195

    Comments posted to this topic are about the item Temporary tables and Table variables storage

  • This was removed by the editor as SPAM

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    Nice and easy question.

  • Derek Slinn

    SSC Enthusiast

    Points: 142

    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

    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: 286960

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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

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

  • Luis Cazares

    SSC Guru

    Points: 183581

    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: 27839

    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: 30385

    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

    Interesting discussion. Thanks!

  • George Vobr

    SSCrazy Eights

    Points: 9018

    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: 27501

    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.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

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

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