Temporary Tables In SQL

 What is a temporary table?

SQL Server provides us with a very unique feature in which a developer can create tables at runtime. These tables are not permanent tables and are called temporary tables. These are used within a scope of a code block. These tables are created inside tempdb database. The temporary tables are the ones which are created for a particular user session. After the transaction is done, the table is removed.

There are two types of temporary tables,

• Local Temporary table
• Global Temporary table

Local Temporary Table

A local temporary table is the one that is created for a single session. It contains a “#” symbol before the table name. The syntax for creating a temporary table is the same as that of creating a normal table in the database.

CREATE TABLE #STUDENT
(
   StudentId INT,
   StudentNameVARCHAR(30),
   Marks int
)

Run the preceding query. Once the query is executed “#STUDENT” table is created. Now, we are going to insert some values into the temporary table. 

INSERT INTO #STUDENT(StudentId, StudentName,Marks) VALUES(1,'Nitin Tyagi',200);
INSERT INTO #STUDENT(StudentId, StudentName,Marks) VALUES(2,'Avant Singh',100);
INSERT INTO #STUDENT(StudentId, StudentName,Marks) VALUES(3,'Akshay Sharma',300);

Execute the Query. Now, to see the values inserted into the temp table, execute the following query.

select*from #STUDENT

Output



Global Temporary table

A global temporary table is the one that uses “##” before the table name. Global temporary tables are visible to all the users or across all the sessions unlike local temporary table. Let us create one global temporary table now.

CREATE TABLE ##GLOBALTEMP
(
   Id INT,
   Text VARCHAR(30)
)

Run the preceding query. Once the query is executed “##GLOBALTEMP ” is created. Now, we are going to insert some values into the GLOBALTEMP table.

INSERT INTO ##GLOBALTEMP (Id, Text) VALUES(1,'Sample Text1');
INSERT INTO ##GLOBALTEMP (Id, Text) VALUES(2,'Sample Text2');
INSERT INTO ##GLOBALTEMP (Id, Text) VALUES(3,'Sample Text3');

Now, to see the values inserted into the GLOBALTEMP, execute the following query.

select*from ##GLOBALTEMP

Output



Where are temporary tables stored?

We can find the temporary tables in tempdb database in the system databases in SQL Server. We have created two temporary tables in this post. We should be able to see both these tables in the tempdb database. Let us verify the same now.

Go to SQL Server Object Explorer-> Expand Databases->System Databases->tempdb->Temporary Tables.



When to use Temporary tables?

Below are the scenarios where we can use temporary tables, 

• When there is a complex join operation.
• When temporary result set is to be stored for further operations in a stored procedure.
• When there is a large usage of data manipulation in the script.
• Cursors can be avoided and thus temporary tables can be used.

In this post, we saw how we can use temporary tables in SQL.