SQL Server temp table

SQL Server temp table

Temp Table

Temporary tables is used to store intermediate results when processing data within a batch or procedure. It is same as normal table. We can perform all kinds of operation on temp tables which are available for normal database tables. We can create a temp table by including a # sign in front of table name. 

Following are the different types of temp tables available in SQL Server:

1. Local Temp Table

2. Gloabl Temp Table

 

Local Temp Table (Starts with #)

 Local temp tables are only available to the instance (coonection) through which it is created. Once the instance (connection) is closed all the local temp tables created during the session are droped automatically.

 /*Local temp Table */

 

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE #MyFirstTempTabel
 
 (
 
  ID INT,
 
  FirstName VARCHAR(100),
 
  LastName VARCHAR(100)
 
 )
 
 GO

Gloabl Temp Table (Starts with ##)

 Global temp tables are available to the all the availabe connections. Once all the instances (connections) are closed the global temp tables created during the session are droped automatically.

 /*Global temp Table */

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE ##MyFirstTempTabel
 
(
 
 ID INT,
 
 FirstName VARCHAR(100),
 
 LastName VARCHAR(100)
 
)
 
GO

Temporary Tables Location

 By default all temporary tables stored in tempdb database of SQL Server. Tempdb database contains temporary table from all the available SQL Server connections. 

0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share