O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required