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:
CREATE TABLE #temp (
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%’
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 * from @tempdb
you can also create global temporary table by define table name with two ‘##’.
intresting isn’t it??