Advertisements
Home > Misconception, tempdb > Table variables are not stored in memory, but in tempdb

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:

SQL Server - Table Variable - YouTube


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: