Types of Temporary Tables in SQL Server 2008
In this article I describe the use of Local Temporary Tables and Global Temporary Tables in SQL Server and how they are created by SQL Server 2008.
Temporary tables are of two types, one is local
and other is global.
Local Temporary Tables
Local Temporary tables are the tables created
under the tempdb database. Local Temporary Tables are denoted with the prefix
# of table name. The scope of Local Temporary Table is to the current
connection of the current user. Once the procedure or session terminates, these
tables are automatically destroyed. For example, for creating local temporary
table named Employee, this will be specified with the prefix #, like
Example of Local Temporary Table:
Global Temporary Tables
Global Temporary tables are the tables created
under the tempdb database. Global Temporary Tables are denoted with the prefix
## of table name. Global temporary tables can be accessed by anyone who
logs into the database as long as the creator of the global temporary tables is
still logged in. It will be automatically dropped when the session that created
it terminates and when other processes which were referencing it, stopped
referencing it. Therefore, if the process that created the table closed, then
also it remains active if other process is referencing it.
Example of Global Temporary Table:
Storage Location of Temporary Table
Temporary Tables are created under the tempdb