What Is A Temporary Table In SQL Server?

Temporary tables are commonly used by database developers and DBAs to store data temporarily. Temporary tables are also known as temp tables in SQL Server. We will be representing as temp table moving forward in this article. Temp tables are generally used for faster data retrieval and increased query performance. Let’s look deeper and learn about the temp tables in SQL Server.

What is a temp table in SQL Server?

The temp table is used to store data temporarily for faster data retrieval. It works like a normal table to perform select, insert, update, or delete commands. Temp tables are created with a prefix with either a single # or a double ##. Additionally, temp tables are automatically dropped when a session is closed in SQL Server.

Types of temp tables

There are two types of temp tables. Local temp table, and a global temp table.

Local temp tables

Local temp tables are created with a single # prefix on a table name. It means that the local temp tables are active and usable to the session that created them. No other user can access the temp table when the session is active upon creating a local temp table. However, if you are creating a local temp table within a stored procedure, the temp table will be dropped automatically.

Creating a temp table and working with DDL/DML commands.

create table #Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into #Temp_Table
select 1, 'Apollo', 'New York'

Update #Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from #Temp_Table where ID=1

select * into #Temp_Table_2 from #Temp_table

drop table #Temp_table

Global temp table

Global temp tables are created with a double ## prefix on a table name. It means that the temp table is active and usable for other users as well. But the only protocol to follow is that the session that opened for creating the global temp table must be open and active for others to access. The moment the master session closes, the global temp table will be dropped, and other users lose access to the global temp table.

create table ##Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into ##Temp_Table
select 1, 'Apollo', 'New York'

Update ##Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from ##Temp_Table where ID=1

select * into ##Temp_Table_2 from ##Temp_table

drop table ##Temp_table

Where are the temp tables stored in SQL Server?

All the temp tables are stored in the tempdb. All the temp tables sit in the temporary tables folder under the tempdb.

Bottomline

Temporary tables are used for faster data retrieval. In many cases, temp tables come handy while optimizing a query. Both local and global temp tables will be stored in the tempdb.

You may also refer:
CTE in SQL server