Temporary Tables in SQL Server

Temporary tables are tables used to hold data for temporary purpose. There are two types of temporary tables.

1. Local Temporary table

2. Global Temporary table

Local Temporary table is a table created with "#" preceding the name of table. It exists till the connection to database, which has created it, lasts. The table is accessible by that connection only. The table is created in temporary database i.e. tempdb. The name of database.tablename should not be more than 116 characters for such tables. Suppose if we create local temporary table with name #tt, then a table with name like "#tt_________________________________________________________________________________________________________________000000000038"

is created in tempdb database. The length of name is 128 characters.

Example : create table #tt ( a varchar(20))

Multiple local temporary tables can be created with same name from different connections. In such case, these tables are also created in tempdb database with name slightly varying last suffix number. A normal programmer will use local temp table more than 90% of times.

Global Temporary Table is a table created with "##" preceding the name of table. It also exists till the connection to database, which has created it, lasts. But the table is globally accessible by other connections also. But, when the connection creating it terminates, then other connections also cannot use it. These tables are also created in tempdb database of SQL Server. It is created with same name as provided by user.

Example : create table ##tt ( a varchar(20))

As the table has global scope, the table name should be unique. Often, the global temp table can be replaced by temporary nature of permanent table. I mean better create a permanent table (with no # and ## in prefix) with a column to identify accessing users like SPID (Stored Procedure ID) or hostname or username to track and separate users. This permanent table has to be wiped out with user data whenever he/she finishes using it. This will remove need to recreate the table every time. In this case, benefit of global temp table becomes that, it does not consume space in database unlike permanent table.