SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth

During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by suggesting following:

Temp Table and Table Variable — both are created in TempDB and not in memory.

Let us prove this concept by running the following T-SQL script.

/* Check the difference between Temp Table and Memory Tables */
-- Get Current Session ID

SELECT @@SPID AS Current_SessionID

-- Check the space usage in page files

SELECT user_objects_alloc_page_count 

   FROM   sys.dm_db_session_space_usage 

   WHERE session_id = 

   (SELECT @@SPID )GO

-- Create Temp Table and insert three thousand rows

   CREATE TABLE #TempTable 

   (Col1 INT)

   INSERT INTO #TempTable (Col1)

   SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)FROM sys.all_objects a 

   CROSS JOIN sys.all_objects b
  GO

-- Check the space usage in page files

SELECT user_objects_alloc_page_count 

    FROM   sys.dm_db_session_space_usage

   WHERE session_id =(SELECT @@SPID )

    GO

 -- Create Table Variable and insert three thousand rows

  DECLARE @temp TABLE(Col1 INT)

  INSERT INTO @temp (Col1)SELECT TOP 3000 ROW_NUMBER() 

  OVER(ORDER BY a.name)FROM sys.all_objects a 

  CROSS JOIN sys.all_objects b
  GO

 -- Check the space usage in page files

  SELECT user_objects_alloc_page_count 

  FROM sys.dm_db_session_space_usage

  WHERE session_id = (SELECT @@SPID )

  GO

-- Clean up

DROP TABLE #TempTableGO

Let us see the resultset. It is very clear that the size of the table variable and temp table is the
same and created in TempDb.

Have you ever heard of this misconception? Do you know any other method to prove that both
 Temp Table and TableVariable are created in TempDB.