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

转自 http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

Recently, I have been conducting many training sessions at a leading technology company in India. 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 #TempTable
GO

 

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.

Update : Ken Simmons have written excellent another article proving without inserting data that temp table and table variable both are created in TempDB please read here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)