I have been asked what is the difference between Temp Tables, Table Variables, CTEs, and Derived tables.  So I thought I would take this opportunity to do a short blog post on each and then summarize the differences.

Today we will be talking about that transient table that stopped by, put down some roots, and then disappeared… the Temp table.

Creation

A Temp table can be created in your queries or procedures by specifying a CREATE TABLE statement just like an actual table.

CREATE TABLE #MyTempTable ( Columns, Contstraints ...)

Storage Location

Temp tables are created and stored in tempdb as physical structures.

--procedure that creates a temp table and calls another procedure
CREATE PROCEDURE dbo.CreateAtempTable
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #MyTempTable (ID INT IDENTITY(1,1) PRIMARY KEY)
INSERT INTO #MyTempTable DEFAULT VALUES
EXECUTE dbo.ReadFromTempTable
END
GO
--procedure returns results from temp table
CREATE PROCEDURE dbo.ReadFromTempTable
AS
SET NOCOUNT ON
BEGIN
SELECT * FROM #MyTempTable
END
GO
--execute the first procedure. you can see you get a record back
EXECUTE dbo.CreateAtempTable


Scope

Once a temp table is created it is available to any subsequent process on that connection. As an example, you can create a temp table in one procedure and reference it in nested procedures, until it is dropped.

You can create and reference temporary tables in stored procedures, but you cannot create or reference a temp table in a scalar or table valued function.  I have read about ways to get around this limitation, but they are not supported by Microsoft and should not be used.

Note: using a double hash in your create will make a global temporary table available to all connections on the server. The table will not be dropped until all referencing connections are closed. To this day I have never found a reason to use a global temporary table in production code. I suppose if you wanted to create a temporary table in a child process and have it available in the parent process it could be a solution, but I cannot think of a situation where that would not be a bad design decision.

Transactions

Temp tables participate in user transactions and as such will be subject to rollbacks and commits where appropriate.

Cardinality

Column statistics are stored for temporary tables. This means that if a certain number of modifications are made to the temporary table the optimizer will recompile statements referencing the temp table. This is nice, because typically when the execution plan was first made you probably did not have any data in the temporary table.

Indexes

You can create indexes and constraints on temp tables. However, just like a real table, indexes will require more disk space, and for temp tables that space will be in tempdb.

CREATE NONCLUSTERED INDEX tmpIX_MyTempTable_SomeColumn ON #MyTempTable ( SomeColumn ) INCLUDE ( OtherColumns)

Logging

Statements impacting a temp table are logged in the tempdb transaction log.

So if I was a temp table and was out looking for a t-shirt that described how special I was it would say something like …

Ok that may be a lot of words for a t-shirt.

Do you have any questions, or tips and tricks for using temp tables?

Let’s discuss in the comments below.