Microsoft® SQL Server® 2012 Bible

406

Part IV: Programming with T-SQL

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-defi ned table, except the tem-

porary 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 tem-

porary 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 identifi er.

Most objects can have names up to 128 characters in length, but temporary tables are lim-

ited to 116 so that the last 12 characters can make the name unique. To demonstrate the

unique name, the following code fi nds all the temporary tables beginning with #Pro stored

in tempdb.sys.objects:

SELECT name

FROM tempdb.sys.objects

WHERE name LIKE '#Pro%';

Result (shortened to save space; the real value is 128 characters wide):

name

-------------------------------------------------------------

#ProductTemp__________________________________00000000002D

Despite the long name in sys.objects, SQL queries still reference any temporary tables

with the original name.

c16.indd 406c16.indd 406 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM

http://www.it-ebooks.info