Temporary tables and Table variables storage

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

  • This was removed by the editor as SPAM

  • Nice and easy question.

  • 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

  • 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.

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

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

  • 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.

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

  • 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
  • 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!

  • 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.

  • Interesting discussion. Thanks!

  • Mike Hays (9/1/2016)


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

    +1;-)

    Interesting question, thanks Sergey 🙂

  • 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.

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

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

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