SQL with Manoj

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: