recommend a good temp table tutorial in SQL Server

Question

Hello everyone,

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.

Answer 2

Here is a nice article about SQL tables:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

It was very useful for me :) Enjoy

Answer 3

The following guide appears pretty good place to start for detailed information about temporary tables (& table variables) including life-time, sharing etc.:

• Temp Tables and Table Variables: When To Use What And Why - Part 1
• Temp Tables and Table Variables: When To Use What And Why - Part 2

Are SQL Server Temp Tables Really Necessary? also looks at using temporary tables for performance reasons.

Comparing them to 'normal' tables i would say the biggest differences is essentially that normal tables persist in the database and thus should be used whenever you need to store the data you are working with, wheras temporary tables should be used if just working within the context of a query/stored procedure etc...

Answer 4

Here is a quick bit of SQL to create a temp table and select from it

-- Create Temp table
CREATE TABLE #temps 
(
 VId int,
 first VARCHAR( 255 ),
 surname VARCHAR( 255 ),
 DOB DATETIME

 PRIMARY KEY (VId)
)

-- Insert some test data
Insert into #temps (Vid, first, surname, DOB) 
VALUES (1, 'Bob', 'Jennings','23 Feb 1970')


-- Insert some test data
Insert into #temps (Vid, first, surname, DOB) 
VALUES (2, 'John', 'Doe','14 Oct 1965')


-- Select data from the temp table
Select * from #temps


-- Run if you wish to drop the table
-- DROP T ABLE #temps