SQL Server Table Variables Won’t Perform Quicker than Temporary Tables

on February 11, 2013

There is a common misconception that table variables are in-memory structures and as such will perform quicker than temporary tables. Thanks to a DMV called sys.dm_db_session_space_usage, which shows tempdb usage by session, you can prove that’s not the case. After restarting SQL Server to clear the DMV, run the following script to confirm that your session_id returns 0 for user_objects_alloc_page_count:

SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
Now you can check how much space a temporary table uses by running the following script to create a temporary table with one column and populate it with one row:

CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
The results on my server (shown in Figure 1) indicate that the table was allocated one page in tempdb.
C 08f 003
 
Figure 1. 
Now run the same script but use a table variable this time:

DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
C 08f 004
 
Figure 2.
As shown in Figure 2, using the table variable caused another page to be allocated in tempdb, so table variables are not created in memory.
Table variables and temporary tables are both likely to be cached, however, so in reality, unless your server is memory constrained and you’re using particularly large tables, you’ll be working with them in memory anyway.

Related Posts

Leave a Reply