Pages

Tuesday 26 April 2011

Whats better, temporary table or table variable?


For some time now, I believed table variables were a better choice than temp tables and that was because I read table variables were in-memory structures. I recently read something otherwise. So I thought I would write this down so it stays “in-memory”.


A DMV useful here is sys.dm_db_session_space_usage, and the columns of interest to us are session_id, database_id and user_objects_alloc_page_count which will show us the pages allocated for objects in tempdb.

Let us create a temporary table as below  then query the sys.dm_db_session_space_usage  DMV to see  the number of pages allocated for this object and the database in which it is allocated.

create table #temptable (COL1 INT)
select session_id, db_NAME (database_id) AS DBName, user_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID

session_id
DBName
user_objects_alloc_page_count
55
tempdb
0

When a create table is run, SQL Server does not allocate page to the table upfront, but rather when the first data is inserted, pages are allocated. So, let us go ahead and insert a single row that is all we need to see the allocation of pages.

insert into #temptable (col1) values (1)

Now, let us run the DMV query again, we get the results below:

select session_id, db_NAME (database_id), USER_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID

session_id
DBName
user_objects_alloc_page_count
55
tempdb
1
We see that 1 page is now allocated to the table #temptable in tempdb.

Let us see if this is the case with table variables as well. So, create a table variable and insert a row in a single batch, (since the scope a table variable is limited to the batch it is declared, unlike a temporary table whose scope is the entire session that created it) as shown below:

DECLARE @TABLEVAR TABLE (COL1 INT)
INSERT INTO @TABLEVAR (COL1) VALUES (1)
GO

Let us query the DMV again,

select session_id, db_NAME (database_id) AS DBName, user_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID

session_id
DBName
user_objects_alloc_page_count
55
tempdb
2

We see that a new page was allocated, and even in case of a table variable, page was allocated in the tempdb and not in the memory.

So both temporary tables and table variables are not in-memory structures, and that being said, let us look at specific leverages temp tables provide as against table variables. 


Temporary tables allow constraints, indexes, statistics, and schema changes on them, just like any permanent table would. Also, "select into" and "insert into ... exec" are allowed with a temp table. 

On the contrary, a table variable allows only constraints, and hence indexes associated with primary key and unique key constraints. Otherwise, we cannot create indexes or statistics; neither can the schema be changed once the table variable is declared. Although indexes, associated with primary key or unique are available, these are not useful, since they are not available at compile time. No statistics, no useful indexes means that the query plan can be bad.

So, with all this information at hand, how do we decide which one to use in our TSQL code or when to use.

Although, temp tables do allow constraints, indexes and statistics, we know for a fact, on small tables, an index scan will be relatively more costly than a table scan. So for smaller tables, we don’t quite see a need for indexes or statistics, and hence making table variables a better choice. Also, if the table is small enough, and assuming there is enough memory, the entire table might well be available in the memory, think about it - I guess that is where this myth about table variables being "in-memory structures" originated. Or more so, because the table variables are declared as variables, and variables have been in-memory objects in programming languages. 


And, needless to say, if you foresee more number of rows, a temporary table is a better choice to make.

3 comments:

  1. good insights !
    keep it coming plz

    ReplyDelete
  2. I dont quite understand one point.. ur sample table was a single row table that should have only take a few bytes of storage.. why wasnt that an in-memory operation

    ReplyDelete
  3. storage is always disk, whether we have 1 row or many. but ultimately it will be read into memory for any processing. So, it is going to be an in-memory operation, but, the table itself is not an in-memory structure, as it is not created in memory, and a lot of sql folks believe table variables, like any variable is a memory buffer(s) allocated. makes sense?

    ReplyDelete