Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 29 February 2012

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.