recommend a good temp table tutorial in SQL Server [closed]

Question

I searched but cannot find a good temp table usage tutorial for a newbie in SQL Server 2005/2008. I want to learn pros and cons of temp table compared with normal table, its life time and how temp table is shared (in the same session, cross sessions)?

thanks in advance, George

Answer 1

There are two ways to create temp table. This one will create the table and insert data from PHYSICALTABLE;

SELECT FIELD1,FIELD2,FIELD3 INTO TempTable FROM PHYSICALTABLE;

The other way is to use CREATE TABLE method;

CREATE TABLE #TempTable (ID int,NAME varchar(50));
INSERT INTO #TempTable(ID,NAME) VALUES(1,'PERSON');

Temp tables will be removed by server once the connection is closed, or when you use DROP TABLE command on them. Unless you use global temp tables (By adding ## into table name), every connection only has access to their own temp tables. I had read temp tables causes performance loss on big tables, so I usually only use temp tables to UNION two tables then group by+SUM those two.