view about temp table in sql server

when ever is require to perform some temporary change in current table without changing actual data in table, SQL server 2005 provides wide range of options for temporary calculations.

Let look at a simple CREATE TABLE statement:

tempID  int,
tempcolumn char(30) )

You’ll notice the prefix of the table name is “#”, This tells the sql server that this table is temporary and it will auttomatically delete when the current session is over.

Temporary table are created in tempdb database. 

there is one important thing to mention here is when temp table being created with specifying primary key name in create statement then the the onther session could not create same temp table with same primary key name. because it will complain “the given name is exist in database”. 

But without specifing key name it always create new table with new primary key with temp number attached with name.

so if you run : 

create table #temp_temp (id varchar(10), tempcol varchar(100))

select * from tempdb..sysobjects where name like ‘%temp_temp%’


name …

#temp_temp________________0000000000B7 …

If another user is creates same name temp table the table being create with #[Table name]__________xxxxxxx.

And if you are using sql server 2000 or higher then you can also use table variable. it is similar to temp tables but  they always stays in memory not in Tempdb database.

declare @tempdb as table (tempId int, tempdb varchar(20))

INSERT INTO @tempdb(tempId,tempdb)

SELECT 12121,’tempdb’

select * from @tempdb 

you can also create global temporary table by define table name with two ‘##’.

intresting isn’t it??


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s