What is a temporary table?

A temporary table is an object that is created and stored temporarily by SQL Server in the tempdb database. The table can be created by any user by using either the CREATE TABLE statement or SELECT…INTO statement. Temporary tables are of the following two types:


1. Local: A local temporary table is created by prefixing the table name with a single hash(#) sign. The table is visible only to the user who created it.

2. Global: A global temporary table is created by prefixing the table name with a double hash(##) sign. The table is visible to all the users who are connected to the database engine.

The syntax of creating a temporary table is as follows:
To create a local temporary table.

•  CREATE TABLE #temporary_table ( column1 datatype, column2 datatype)
•  SELECT * INTO #temporary_table FROM original_table

To create a global temporary table.

• CREATE TABLE ##temporary_table (column1 datatype, column2 datatype)
• SELECT * INTO ##temporary_table FROM original_table