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 (

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 ...

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.