SQL Server | Table Variable are stored in TempDB

Today I am going to talk about the most common misconception about the table Variables. Most of the people doesn’t know about where table variables are stored? If you ask them they will say memory, RAM, Main memory or something like that. And more over if you explain them that they are stored in TempDB, they will not agree with you and may go one step further saying you don’t have knowledge about table variables, LOL:). Anyways today I am going to explain you where table variables are stored and how we can verify it.

Example 1 – Here you can easily see what tables you have in your TempDB before table Variable creation and after table variable creation. Please note that I am using 3 part name in the below queries.

--

SELECT * From TEMPDB.sys.tables
GO

--Create a Table Variable
DECLARE @tableVariable TABLE
(
	 Id INT 
	,Name VARCHAR(20)
)

SELECT * From TEMPDB.sys.tables
GO

--

Output

Pawan Khowal – Table Variables & TempDB

Example 2 – Physical Location – Here you can see table Variables data in TempDB. Please note that I am using an undocumented DBCC flag. Do not use this on production.

--

DECLARE @Id TABLE
(
	 Id INT 
	,Name VARCHAR(20)
)

INSERT INTO @Id (Id,Name) VALUES
(1,'Pawan'),
(2, 'Sharlee')

DECLARE 
	  @fileId INT = 0
	, @pageId INT = 0

SELECT 
	  DISTINCT @fileId = file_id
	, @pageId = page_id
FROM @Id 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

DBCC TRACEON(3604)

DBCC PAGE('TEMPDB', @fileId, @pageId, 3)

DBCC TRACEOFF(3604)

--

Output

Pawan Khowal – Table Variable Physical Location

Enjoy! I hope now it is clear that table variables are stored in tempDB. Cheers !!

Pawan Kumar Khowal

MSBISKills.com