Temporary Tables in SQL

• Introduction
• Types of Temporary Tables
  • Local Temp Table
  • Global Temp Table
• Creating Temporary Table in SQL Server
• Storage Location of Temporary Table
• When to Use Temporary Tables?

Introduction

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

• Local Temp Table
• Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Creating Temporary Table in SQL Server

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server :

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in thetemporary table similar to a general table like:

insert into #LocalTempTable values ( 1, 'anna','Paris');

Now select records from that table:

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select"Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##"with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:  

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in thetemporary table similar to a general table like:

insert into ##NewGlobalTempTable values ( 1, 'ANNA','Paris');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Storage Location of Temporary Table

When to Use Temporary Tables?

Below are the scenarios where we can use temporary tables:

• When we are doing large number of row manipulation in stored procedures.
• This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
• When we are having a complex join operation.