Question: Where is Table Variable Created?
Answer: On Hard Disk and NOT in Memory.
In recent time, I have received this question more times than the past and I believe the reason for it is the recent blog post series which I have done on the performance of the temporary tables. If want to read more about that topic, I suggest you read the following blogs:
- SQL SERVER – Table Variable or Temp Table – Performance Comparison
- SQL SERVER – Regular Table or Temp Table – TempDB Logging Explained
- SQL SERVER – Regular Table or Temp Table – A Quick Performance
Now lots of people believe that variables are created in memory. While that is true in most cases it is not true in the case of the table variables. Just like how temporary tables are created in the TempDB disk the same way table variables are also created in the TempDB disk.
Here is the blog post I strongly suggest you read which I have written to prove that Table Variables are created in Memory is a MYTH: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth. In that blog post with detailed explanation and with the help of T-SQL script I have proved that table variables are similar objects as the temporary table.
Here are two more blog posts on the same topics SQL SERVER – Is tempDB behaving like a Normal DB? and SQL SERVER – Inside Temp Table Object Creation, which also discusses the about the temporary tables. In one of the email interactions, one of my readers asked – “Is there a way to know table variables are created? Are objects created stored as part of TempDB? Are they written to the T-Log?” Here is the blog post which is written to answer the same question: SQL SERVER – Watching Table Variable Data in TempDB. I suggest that you read the blog post to get an idea how table variable data is stored in TempDB.
Let me know if you have any other question about the table variable and I will be happy to extend this series and post about it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
I remember that long time ago a read something about table variables saying that when a small amount of data is inserted, it is handled in “Memory”, on the other hand, when a large amount of data is inserted, it will be treated as a Temp table, being created on Hard Disk. I’m not sure if it was true.