Is table variable stored in tempdb?

• Teemo

  Ten Centuries

  Points: 1376

  More actions

  October 13, 2011 at 8:31 pm

  #249178

  hello guys,

  Is it true that when you created a table variable, and inserted a data on that table variable all stored on tempdb? same as temp table(#table)?

  thanks in advance

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 13, 2011 at 8:44 pm

  #1394427

  REad this and try the code to learn

  http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

  or this one .. does not require as much code

  http://cybersql.blogspot.com/2009/12/yes-table-variables-and-temp-tables.html

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  Follow

  October 13, 2011 at 9:09 pm

  #1394431

  Teemo (10/13/2011)

  ---

  hello guys,

  Is it true that when you created a table variable, and inserted a data on that table variable all stored on tempdb? same as temp table(#table)?

  thanks in advance

  Yes... and so are Temp Tables. What many don't know is that both Table Variables and Temp Tables both start out physically living in memory and, if they get too big, "spool" to disk.

  Please see the articles that BitBucket pointed you to and then read the following excellent article written by Wayne Sheffield on other differences and similarities between Table Variables and Temp Tables...

  http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)