Temp Table -Temporary Temp Tables

1)They can be created at run time in Tempdb database.

2)DDL,DML statements like select,update which can be executed on regular tables can be done on temporary tables. They can have constraints and can be indexed.

3)Stored procedure make use of Temporary Temp Tables. In Stored procedures data is manipulated via complex query. In case of complex queries, temporary table is used to store the output of one query / intermediate results, which then can be used for further manipulation via joins etc to achieve the final result.

Two types of temporary tables:

1)Local Temp Table

Syntax to define Local Temp Table

CREATE TABLE #LocalTemp
(
 column-list
)

The Local temp table visibility is limited to the current query window or current session that created it. An error is raised if user try to use temporary table in other query window other then the query window that created it. The temporary table is automatically deleted if current query window that created temporary table is closed.

2)Global Temp Table

Syntax to define Global Temp Table

CREATE TABLE ##GlobalTemp
(
 column-list
)

They are visible to all sql query window after they are created, and deleted when sql server sessions or query window that created it have been closed.

Table variable

Syntax for Table variable:

DECLARE @TableVariable TABLE 
(
 column-list;

)

Table variable is a local variable. It's scope is limited to function,procedures and batches of query execution in which it is defined. Table variable is not stored in the memory but is automatically stored in Tempdb database. Table variable allows to create primary key, identity at the time of Table variable declaration but does not allow to create non-clustered index. DDL,DML statements cannot be applied to Table variable.