Temporarytables

TEMPORARY TABLES :

Temporary tables are very similar to permanent tables,permanent tables get created in the database you specify,and remains in the database permanently ,until you delete or drop them.On the other hand temporary tables get created in the TempDb and are automatically deleted when they are no longer use.

Different types of Temporary tables :

1.local Temporary tables 2.Global Temporary tables

1.LOCAL TEMPORARY TABLES :

A Local temporary table is available, only for the connection that has created the table .

A Local temporary table is automatically dropped,when the cnnection that has created the Local temporary table , is closed.

A local temporary table starts with '#'

create table #temp(columnname datatype)

ex :

create table #temp1 (id int, name varchar(50))

If user wants to explicitly drop the temporary table, by using

Drop table #temptable

2.GLOBAL TEMPORARY TABLES :

To create Global temporary table , prefix the name of the table with'##'.

create table ##temp(columnname datatype)

create table ##temp(id int,name varchar(100))

Global temporary table are visible to all connections of the sql server , and are only destroy when the last connection referencing the tables is closed.