Advanced Relational Database /* Temp Tables and Table Variables */ 1/19/11

use DB
Create Table Frogs (
  ID int primary key identity(1,1)
 ,Legs int not null
 ,Color varchar(8982)
  )

* look up temp tables and table variable
--syntax
--when to use

 4 ways to store data: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name). 

Local Temporary Tables

Create Table #pimp
(
  id int
  ,daddy varchar(50)
)

A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it.

Table Variables
Declare @people table
(
  id int
  ,name varchar(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.


I found all of my information here.