Temporary Tables in SQL

Temporary Tables are created under the tempdb database.

Types of Temporary Tables:

1. Global Temporary Table (represented by ##)
2. Local Temporary Table (represented by #)
3. Table Variable

1. Global Temporary Table

Global Temporary Tables are created under the tempdb and will be represented by ##.

Say suppose I am creating a Global Temporary Table named employee so it will be like ##employee.

We can use a Global Temporary Table everywhere; i.e. in a query, SP etc. except not in Functions.

When we use a Global Temporary Table in a query the scope of the table is through all the active sessions in Management Studio. We can create an index on the Global Temporary Table. We can use the nolock clause with a Global Temporary Table.

The syntax to create a Global Temporary Table:

create table ##employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

2. Local Temporary Table

Local Temporary Tables are created under the tempdb and will be represented by #.

Say suppose I am creating a Local Temporary Table named employee so it will be like #employee.

We can use a Local Temporary Table everywhere; i.e. in a query, SP etc. except not in Functions.

When we use a Local Temporary Table in a query the scope of the table is to the active session only in Management Studio. Where we have created a Local Temporary Table or we are directly inserting into it. We can create an index on the Local Temporary Table. We can use the nolock clause with a Local Temporary Table.

The syntax to create a Local Temporary Table:

create table #employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

3. Table Variable

A Table Variable is used like a variable where we declare a variable of type table.

Say suppose I am declaring the Table Variable.

declare @employee Table
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

The Table Variable is used like a table.

We can use a Table Variable everywhere including functions. We can't create indexes on Table Variables.

We can't use the nolock clause with Table Variables.

Generally we used above #1, #2 & #3 when we are dealing with performance because using this in a query improves the performance of a query.