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 ##.

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 in Functions.

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

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 #.

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 in Functions.

When we use a Local Temporary Table in a query the scope of the table is the active session only in The 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.

Syntax to create a Local Temporary Table:

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

3. Table Variable

The table variable is used like a variable where we declare a variable of type table.

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 worked like a table.

The table variable we can use everywhere including functions. We can't create the indexes on a table variable.

We can't use the no lock clause with Table Variables.

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