Temporary Tables

Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (

YakID int,

YakName char(30) )

Select * from #yaks

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in tempdb. If you run this query:

CREATE TABLE #Yaks (

YakID int,

YakName char(30) )

select name

from tempdb..sysobjects 

where name like '#yak%'

You'll get something like this:

name

------------------------------------------------------------------------------------ #Yaks__________________________________________________________00000000001D

(1 row(s) affected)

drop table #yaks

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs(##). For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.  These are rarely used in SQL Server.

CREATE TABLE ##Yaks (

YakID int,

YakName char(30) )

Select * from ##yaks

What is the difference between a Local and a Global temporary table?

Local temporary tables	Global temporary tables
Created  with One pound signs(#). .	. Created  with two pound signs(##).
Multiple users can’t share a local temporary table, Only available to the current Db connection for current user and are cleared when connection is closed	Available to any connection once created. They are cleared when the last connection is closed Can be shared by multiple user sessions. But when sessions close table removed.