Temp tables and table variables

I’ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I’m knee-deep in some AI stuff at the moment) I thought I’d quickly go over some points on temp tables and table variables.

Temporary Tables

  • Created using the Create table syntax, preceding the table name with a’#’ for a local temp table and ‘##’ for a global temp table
  • Allocated storage space within the TempDB database and entered into the TempDB system tables 1
  • The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name
  • Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults)
  • May not have triggers.
  • Foreign keys are permitted, but are not enforced
  • Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables
  • Exist until they are dropped, or the connection closes.
  • Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures
  • Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache

Table Variables

  • Created with a Declare statement, prefixing the table name with ‘@’, like all other variables.
  • Allocated storage space within the TempDB database and entered into the TempDB system tables
  • The table variable’s name within TempDB starts with a #, followed by a hex string.
  • Can have a primary key and defaults. May not have constraints or indexes
  • May not have triggers or foreign keys.
  • Do not have column statistics maintained on them 2
  • Exist while they are in scope and are automatically dropped when they go out of scope.
  • Are not visible in any procedures other than the one they were created in
  • Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache
  • Cannot be truncated
  • Do not take part in transactions. Data modification done to a table variable within a transaction will remain if the transaction is rolled back 3

(1) As seen with the following code

select * from tempdb.sys.tables
go
DECLARE @Test1 TABLE (id int)
SELECT * FROM tempdb.sys.tables
GOCREATE TABLE #Test2 (
id int

SELECT * FROM tempdb.sys.tables
GO

drop table #Test2

(2) Because of this, the query optimiser has no idea, when a query runs, how many rows are in a table variable. It estimates 1. If there are a lot of rows in the table variable, this can result in a very poor plan and very poor query performance.

(3) As seen with the following

DECLARE @Test1 TABLE (id int)
BEGIN TRANSACTION
INSERT INTO @Test1 VALUES (1)
ROLLBACK TRANSACTION
SELECT * FROM @Test1  -- 1 row

4 Comments

  1. Rich

    Just for interests sake, an extension of point 2 above – they appear to remove the choice of parallelism, effectively making the query maxdop 1. Least, in 2k anyway.

    Reply
  2. Gail

    Interesting. I hadn’t noticed that. It could be that, because the optimiser thinks there’s only one row, it doesn’t see a need for parallelism.

    Thanks for pointing that out. I’ll see if I can confirm if that is still the case on SQL 2005.

    Reply
  3. Pradnesh

    Great article

    Reply
  4. Kevin K

    The quote “Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache” seems to contradict another article written for this website called “A Trio of Table Variables” that shows a table variable always being written to disk even when there is no memory pressure. Can you explain the difference for me and clarify whether table variables and temp tables are always written to disk even when RAM is available?
    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *