Table Variables Are Only in Memory: Fact or Myth

October 13, 2009 at 8:40 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , )

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:

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:

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.


  1. Tom Groszko said,

    There are some other differences like logging and the tempdb locks used to create a table that can be very important.

  2. scarydba said,

    True. I phrased that badly since I knew of other differences. I’ll edit the post. Andrew Kelly is tweeting a bunch right now: transactions, as you said, & log data for rollbacks. I only wanted to concentrate on the data storage so I was improprerly dismissive in the language.

  3. Arnie said,

    Very difficult to read this page. white (yellow) type on a black background is virtually impossible on a 1200×1600 montor.

  4. Jon Crawford said,

    Arnie, don’t anger the scarydba…

  5. My Weekly Bookmarks for October 16th | Brent Ozar - SQL Server DBA said,

    […] Table Variables Are Only in Memory: Fact or Myth – Grant Fritchey demolishes another popular performance tuning myth. […]

Leave a Reply

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

You are commenting using your 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: