where is table variables stored in the database?

All Replies

• Tuesday, February 19, 2013 4:38 AM

   

   
  

  0

  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 Muni329 Tuesday, February 19, 2013 7:07 PM
  •  
  • Reply
  • Quote

   
• Tuesday, February 19, 2013 4:54 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Tuesday, February 19, 2013 5:37 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Tuesday, February 19, 2013 5:53 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Tuesday, February 19, 2013 6:52 AM

  Moderator

   

   
  

  0

  > 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 TothMicrosoft Community Contributor, Moderator Tuesday, February 19, 2013 7:04 AM
  •  
  • Reply
  • Quote