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.

My encounters with DBCC CHECKDB - 1

After recovering from a disk crash on one of the production SQL Server 2000 servers, I ran DBCC CHECKDB on every database to ensure we don’t have any errors. It turned out otherwise, and we did have 2 consistency errors reported.

The part of DBCC CHECKDB output containing the error is copied below:

Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 2: Page (6:23238457) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 2, page (6:23238457), row 10. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28416 and 37.

And the last part of CHECKDB output, summarized as below:

CHECKDB found 0 allocation errors and 2 consistency errors in database 'MIRA_OTHERS_CONTENT_DB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MIRA_OTHERS_CONTENT_DB ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To start with, here is some info pertaining to SQL Server, using SELECT @@VERSION

Microsoft SQL Server  2000 - 8.00.2055 (Intel X86)   Dec 16 2008 19:46:53   Copyright (c) 1988-2003 Microsoft Corporation  Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The database involved is called: MIRA_OTHERS_CONTENT_DB;

Using the object ID value from the above output, I could find out the table on which errors were reported.

SELECT OBJECT_NAME (1993058136) as TableName

TableName: Docs

Going back to output above, it was obvious that the error was more precisely on an index, index id 2. Using this information and the object id, one could easily identify the index name affected:

USE MIRA_OTHERS_CONTENT_DB;

SELECT NAME AS IndexName from SYSINDEXES WHERE ID = 1993058136 and INDID = 2;

Returned, IndexName: Docs_IdUnique

Next, I ran SP_HELPINDEX Docs, for better understanding of the index definition.

Index Name	Index Description	Index Keys
Docs_IdUnique	nonclustered, unique, unique key located on PRIMARY	Id
Docs_PK	clustered, unique, primary key located on PRIMARY	SiteId, DirName, LeafName

Once the index name and the table name are known, all one has to do is to just re-create the non clustered index. We could use DBCC DBREINDEX or CREATE INDEX… DROP_EXISTIN to re-create the non-clustered index to eliminate corruption errors as below:

Option 1:

DBCC DBREINDEX ( ‘Docs’,’ Docs_IdUnique’)

Option 2:

CREATE UNIQUE   INDEX [Docs_IdUnique] ON [dbo].[Docs] ([Id])

WITH   FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]

Initially, I thought I could manually drop and create the index, as shown below

DROP INDEX Docs. Docs_IdUnique

But this statement failed, since the unique index is bound by the unique constraint. So then I thought I will drop the constraint. So I ran,

ALTER TABLE Docs DROP CONSTRAINT Docs_IdUnique

And to my surprise, I figure this index enforces the full-text key for Docs table

Cannot drop index 'Docs_IdUnique' because it enforces the full-text key for table 'Docs'.

At that time, I thought over, and figured I could use one of the above two options. If they did not work, I would do a drop and create the clustered index, which in SQL Server 2000 would force the non-clustered index to be rebuilt.

Note:  I would like to remind, that rebuild index behavior is different in SQL Server 2005 and 2008.