Partager via

Table variables are not stored in memory, but in tempdb

  • Article

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:

CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO 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

1st batch of stmt gives me 12 rows with following table names:

##0171552
##0610305
##1010109
##1324600
##1332257
##REPLMSTR
##REPLMSTR2
#tempTab____________________________________________________________________________________________________________000000019D39
DEX_LOCK
DEX_SESSION
sysconstraints
syssegments

… and the 2nd batch gives me 13 rows:

##0171552
##0610305
##1010109
##1324600
##1332257
##REPLMSTR
##REPLMSTR2
#0C4F413A
#tempTab____________________________________________________________________________________________________________000000019D39
DEX_LOCK
DEX_SESSION
sysconstraints
syssegments

This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.

Source: from my personal blog SQLwithmanoj: https://sqlwithmanoj.wordpress.com/2010/07/20/table-variables-are-not-stored-in-memory-but-in-tempdb/