Table variables are not stored in memory, but in tempdb
Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.
–> To prove this I executed following code:
-- Batch #1 SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #2 CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #3 DECLARE @tabVar TABLE (i INT) INSERT INTO @tabVar VALUES (1) SELECT * FROM @tabVar SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO DROP TABLE #tempTab GO
Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.
Batch #2: The second batch of stmt gives me 1 row with following table name:
#tempTab_____________________________________________________________000000019D39
Batch #3: and the third batch of stmt gives me 2 rows with following table name:
#tempTab_____________________________________________________________000000019D39 #0C4F413A
This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.
–> Check the full demo here:
Categories: Misconception, tempdb
Table Variables, Temp Tables, tempdb, Temporary Tables
Comments (0)
Trackbacks (2)
Leave a comment
Trackback
-
November 6, 2013 at 4:51 pmTable variables are not stored in memory, but in tempdb - SQL with Manoj - Site Home - MSDN Blogs
-
October 23, 2015 at 5:29 pmDifference between Temp Table and Table Variable, which one is better in performance? – MSDN TSQL forum | SQL with Manoj