Temporary Tables

Tables are created in the tempdb and are automatically deleted when they are no longer in use.


Two Types of Temporary Tables.

1. Local Temporary:

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available

only to the session that created them. These tables are automatically destroyed at the termination of the procedure orsession. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.

Example:

1. create table #table_name  
2. (  
3. column_name varchar(20),  
4. column_no int  
5. )

2. Global Temporary:

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.

Example:

1. create table ##GlobalTemporaryTable  
2. (  
3. column_name varchar(20),  
4. column_no int  
5. )