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 ...
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.