locked
where is table variables stored in the database? RRS feed

  • Question

  • Hi,

    is table variables stored in database & is it visible as like temp tables(stored in tempDB)?

    if yes, could you please let me know where it will be.

    Thanks in advance

    Regards

    Muni

    Tuesday, February 19, 2013 4:22 AM

Answers

  • select count(*) from tempdb..sysobjects
    go
    declare @t table ( a int )
    select count(*) from tempdb..sysobjects
    go


    http://www.t-sql.ru

    • Marked as answer by Chantan Tuesday, February 19, 2013 7:07 PM
    Tuesday, February 19, 2013 4:38 AM

All replies

  • select count(*) from tempdb..sysobjects
    go
    declare @t table ( a int )
    select count(*) from tempdb..sysobjects
    go


    http://www.t-sql.ru

    • Marked as answer by Chantan Tuesday, February 19, 2013 7:07 PM
    Tuesday, February 19, 2013 4:38 AM
  • Yes, table variables just like temp-tables are stored in tempdb database.

    Check this blog post: http://sqlwithmanoj.wordpress.com/2010/07/20/table-variables-are-not-stored-in-memory-but-in-tempdb/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Tuesday, February 19, 2013 4:54 AM
  • Hi there,

    Both temp table and table variables will be stored in the tempdb database.

    There are questions about what is the max size of the table variable, max size of the temp table. according to online blogs and experts comments both table variable and temp table have a storage limit of tempdatabase size.

    How ever when dealing with transactions temp tables have some advantages.

    check the link below:

    http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

    thanks

    kumar

    Tuesday, February 19, 2013 5:37 AM
  • Hi Muni,

    Both temp table and table variable gets stored in tempdb database, you can check them using this..

    select COUNT(*) from tempdb..sysobjects


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Tuesday, February 19, 2013 5:53 AM
  • > is it visible as like temp tables(stored in tempDB

    Table variables are not visible like temporary tables.

    Table variables scope is a batch, a stored procedure or a UDF.

    Temporary tables scope is a connection(session), a stored procedure or until it's dropped.

    Article: http://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Tuesday, February 19, 2013 7:04 AM
    Tuesday, February 19, 2013 6:52 AM
  • But why it's created in Tempdb and not stored in memory only until and unless their is memory crunch?

    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Tuesday, March 29, 2016 4:35 PM
  • select count(*) from tempdb..sysobjects
    go
    declare @t table ( a int )
    select count(*) from tempdb..sysobjects
    go


    http://www.t-sql.ru

    I got two questions:

    1.Why are temp variables being stored in tempdb?As per my understanding they should have kept in memory and spill over to tempdb in case of memory crunch.

    2.Why are temp variables not showing in the ssms in tempdb ?


    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Tuesday, March 29, 2016 4:43 PM
  • Table variables aren't really stored. They exist only for the duration of the batch that created them. Once it's executed, they're gone.

    Table variables would show in SSMS if you were quick enough:

    this image shows the table variable I created in another process in tempdbs temporary tables.

    I used this to create, and hold it:

    DECLARE @myTableVar TABLE (someUniqueColumnName INT)
    WAITFOR DELAY '2:00'

    I also created the temp table shown in the image, with the same unique column name (so I could be sure to find the right tables).

    You can read more about tempDB here.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Tuesday, March 29, 2016 5:26 PM
  • Yes, they are.

    However, CTE's are not stored

    Tuesday, March 29, 2016 5:28 PM
  • @psingla, Check this video to get hands-on clarification on "Table variables & Temp Tables are created in tempDB"

    https://www.youtube.com/watch?v=oqjEm15RCu8


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page


    Tuesday, March 29, 2016 6:02 PM
  • Table variables aren't really stored. They exist only for the duration of the batch that created them. Once it's executed, they're gone.

    Table variables would show in SSMS if you were quick enough:

    this image shows the table variable I created in another process in tempdbs temporary tables.

    I used this to create, and hold it:

    DECLARE @myTableVar TABLE (someUniqueColumnName INT)
    WAITFOR DELAY '2:00'

    I also created the temp table shown in the image, with the same unique column name (so I could be sure to find the right tables).

    You can read more about tempDB here.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Thanks Partick for the answers but I was thinking why the table variable being created at all in the tempdb? As the name suggests they should created in memory and only in rare cases (like memory is full) these should moved to tempdb.

    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful


    • Edited by psingla Wednesday, March 30, 2016 3:43 AM
    Wednesday, March 30, 2016 3:39 AM
  • The Table variables are only stored in memory see the article: https://msdn.microsoft.com/en-us/library/dn535766.aspx
    Wednesday, March 30, 2016 6:18 AM
  • @Jason_Clark03, the link that you have provided is of a new feature added in SQL Server 2014.

    The one that your referring to are Memory-Optimized Table-Variables, which are stored in memory like In-Memory/Hekaton tables. I've put up a blog post on how to work with these, check here: http://sqlwithmanoj.com/2015/07/31/memory-optimized-table-variables-in-sql-server-2014-and-2016/

    Normal Table-Variables are store in tempDB only, just like #Temp-Tables., check here and https://www.youtube.com/watch?v=oqjEm15RCu8.


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page



    Wednesday, March 30, 2016 6:33 AM
  • "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). "

    https://support.microsoft.com/en-us/kb/305977


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, March 30, 2016 3:16 PM