Temporary Tables in SQL Server

create table #table_name  
(  
column_name varchar(20),  
column_no int  
)

create table ##GlobalTemporaryTable  
(  
column_name varchar(20),  
column_no int  
) 

Both tables are stored in the tempdb system database.

Where is the temporary table stored in SQL Server?

Temporary tables are stored inside the Temporary Folder of TempDB. Whenever we create a temporary table, it goes to the Temporary folder of the tempdb database. tempdb -> temporary tables.

Temporary Tables vs Regular Table in SQL Server

What is the difference between a temp table and a regular table?

Difference between Temporary Tables VS Regular Table

A Temporary table differs in the following two ways from regular tables:

1. Each temporary table is implicitly dropped by the system.
2. Each temporary table is stored in the tempdb system database. The table variable doesn't.

How to Drop Temporary Tables in SQL Server?

We can use the DROP SQL statement to drop a temp table.

Drop Temporary Tables

We can delete the temporary tables using a DROP TABLE command as follows.

DROP TABLE #temporaryTable  
Print 'Deleted table'  

Output

Drop Multiple Temporary Tables

You can drop a single Temporary Table or multiple Temporary Tables at a time.

Syntax

DROP TABLE #table_name1,#table_name2

When to use a Temporary Tables in SQL Server?

A temporary table is used to store data within a session from multiple tables using complex queries and where you need data manipulation on the server side before the data can be returned. For example, if you need to select data from multiple tables, apply a business logic on some columns, also the selection can be changed based on the calculated results. Once you are done with this temp data, the table is dropped form the server. There is no permanent storage of temp tables.

Another example is, if you have a store proc that involves data manipulation using complex SQL queries.

Difference between a temp table and a table variable?

Table variables in SQL are local variables that are created using a DECLARE statement and are assigned values using a SET or SELECT statement. The variables exist in the TempDB with their value as long as their scope exist but once they are out of scope, they are dropped.

Difference between Temporary Table and Table Variable

Conclusion

In this article, we learned about Temporary Tables and how to create them in SQL Server.

Next: Temporary Tables and Table Variables in SQL Server