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 #Employee.
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 database.