Table Variables Are Only in Memory: Fact or Myth

I keep seeing these concepts that have long been disproven, posted again and again on newsgroups as if they were valid information. The latest? Table variables are better because they’re only in memory where as temporary tables write to the disk through tempdb. This one is abjectly wrong. I’m not even going to hedge with “it depends.” From a macro point of view, there are only a few differences between temporary tables and table variables, statistics being the biggest. Temporary tables have ’em and table variables don’t. Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size. Some of the minor differences, and why you might want to use table variables over temporary tables, table variables won’t cause a statement recompile while temporary tables will, table variables don’t participate in transactions and log data for a rollback is not maintainted for temp tables. There are a number of other uses and functions around table variables, but they’re not applicable to this discussion. The point here is that table variables do not live only in memory.

Some of you don’t believe me, of course. So let’s prove this one, again. Here’s a very simple query. It creates a temporary table and loads one hundred thousand rows of data into it. The query does all this within an open transaction so that we can investigate where the data is stored, at our leisure:

BEGIN TRAN

SELECT TOP 100000
IDENTITY (INT,1,1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sc1
,master.dbo.syscolumns AS sc2

This will leave the temp table open and created. The only thing remaining is to determine where the data is stored. To do this, we can query sys.dm_db_session_space_usage. This DMV shows the pages allocated and deallocated to the tempdb. If I run a simple select against the DMV, I’ll see 233 pages in the user_objects_alloc_page_count for the session I’m currently connected as. That represents the data from the temporary table, stored within tempdb. I’ll rollback the transaction and close the connection. Then, I’ll add the following code to the end of the query in a new window with a whole new connection. I do this to avoid any possibility that the temporary table is still within context. Here’s the addition:

DECLARE @temp TABLE(
ID INT )
INSERT INTO @temp
(ID)
SELECT n
FROM #Nums;

Now I’ll run the whole query, creating two identical tables, a temporary and a variable, with identical data. When I look at sys.dm_db_session_space_usage, I now see 466 pages in the user_objects_alloc_page_count for the new connection. That’s because the tempdb is now holding two tables worth of data instead of one.

Or, in other words, table variables are stored in tempdb like temporary tables.

EDIT: Modified the description of the differences between table variables & temp tables.