Temporary tables

Temporary Tables

CREATE TABLE #Yaks
(YakID int,
YakName char(30)
)

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in tempdb

select name from tempdb..sysobjects where name like '#yak%'

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE
(YakID int,YakName char(30)
)

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakNameFROM dbo.Yaks WHERE YakType = 'Tibetan'

Table variables don't need to be dropped when you are done with them.

Which to Use
- If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.

- If you need to create indexes on it then you must use a temporary table.

- When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

----------------------------------------------------------------------------------
http://www.sqlteam.com/article/temporary-tables