Temp Table / Temporary table

Temp Table or Temporary table is one term.

When you required to use the current result set in next queries then store result into Temp Table.

To store Result set for a temporary purpose then we can use temporary table.

we will go through some questions on it.

How to Create?

How to use it?

Where it is Stored?

Can we create Index on it?

Can we create constraint on it?

Can we Create temp table in Stored Procedure? 

Can we Create temp table in Function?

How to Drop it? 

scope

Name overwritten

Types of temp table

local temp table

global temp table

How to Create?

CREATE TABLE #Emp

(  

Emp_Id INT primary Key,  

Emp_Name VARCHAR(30),  

Project_Name VARCHAR(30)  

)  

Insert Into #Emp values(1,'ABC','Project1')

Insert Into #Emp values(2,'LMN','Project2')

Select * from #Emp 

How to use it?

Select * from #Emp 

we can join #Emp with other tables easily.

Where it is Stored?

temp table is stored in Temp Database (one of system Database)

Can we create Index on it?

Yes, We can create index on Temp table while creating or after creation.

Can we create constraint on it?

Yes, We can create  constraints on Temp table,

constraints are as follow,

Primary key

Unique key

Default

Not NULL

Check

foreign key we can't create on temp table.

CREATE TABLE #Emp

(  

Id INT NOT NULL primary key,

Emp_Id INT Unique,  

Emp_Name VARCHAR(30) Default 'NA',  

Project_Name VARCHAR(30) Check(Project_Name<>'NA')

) 

Can we Create temp table in Stored Procedure? 

yes, we can create temp table in stored proc.

Can we Create temp table in Function?

No, because DML operations not allow on physical table. Insert, update, Delete side effective operators so we can't temp table in function.   

How to Drop it? 

1) Drop table #Emp

2) if we use in SP then SP execution finish it will drop automatically.

3) Session - when we close it will drop automatically ( local temp table).

scope

1) local session

2) global session 

Name overwritten