Microsoft SQL Server 2012 Bible

Temporary Tables and Table Variables

Temporary tables and table variables play a different role from standard user tables. By their temporary nature, these objects are useful as a vehicle for passing data between objects or as a short-term scratch-pad table intended for temporary work.

Local Temporary Tables

A temporary table is created the same way as a standard user-defined table, except the temporary table must have a pound, or hash, sign (#) preceding its name. Temporary tables are actually created on the disk in tempdb:

CREATE TABLE #ProductTemp (
 ProductID INT PRIMARY KEY
 );

A temporary table has a short life. When the batch or stored procedure that created it ends, the temporary table is dropped. If the table is created during an interactive session (such as a Query Editor window), it survives only until the end of that session. Of course, a temporary table can also be manually dropped within the batch. However, a temporary table may have many characteristics of a permanent table, including primary keys and clustered and nonclustered indexes.

The scope of a temporary table is also limited. Only the connection that created the local temporary table can see it. Even if a thousand users create temporary tables with the same name, each user sees only his temporary table. The temporary table is created in tempdb with a unique name that combines the assigned table name and the connection identifier. Most objects can have names up to 128 characters in length, but temporary tables ...