Temporary tables are useful when we want a manipulated data in tabular form at run time in query processing. A temporary table is visible to the session in which they were created and are automatically dropped when that session logs off.

Type of temp table

1) Local temp table: Syntax (create table #t).

If a temp table exist in a stored procedure it is automatically dropped when SP ends, i.e. temp table exist in current scope and current connection.

2) Global temp table: Syntax (create table ##t).

Global temp table can be accessed in any session in current connection and can be dropped in any session.

3) Temp variable: Syntax (create table @t)

A temp variable has all features of temp table but

It can’t be dropped manually,they are dropped automatically.

We can use a temp variable in user defined function but can’t do with temp table.