Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 216

TABLE VARIABLES ARE NOT CREATED IN MEMORY

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 coni rm 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 8-3) indi-
cate that the table was allocated one page in tempdb.

FIGURE 8-3

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 ;

As shown in Figure 8-4, using the table variable
caused another page to be allocated in tempdb, so
table variables are not created in memory.

FIGURE 8-4

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.