SQL in the Wild

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 ¹
• 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 ²
• 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 ³

(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