What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

Question

What is the difference between:

CREATE TABLE #temp ( [ID] INT)

INSERT INTO #temp
SELECT ...

and

DECLARE @temp TABLE ( [ID] INT)

INSERT @temp
SELECT ...

in SQL Server 2008?

Answer 1

This is a pretty good reference on the different temp tables

Temp Tables vs Variables

Answer 2

1. There is no log for table variables
2. Table variables have only local scope (you cannot access the same table variable from different procedures)
3. Procedures with temporary tables cannot be pre-compiled

For more details see http://www.sql-server-performance.com/articles/per/temp%5Ftables%5Fvs%5Fvariables%5Fp1.aspx

Answer 3

Temporary tables are like ordinary tables in most characteristics, except they go into TempDB instead of the current Database, and they dissapear after limited scope, (depending on whether they are session based or global Temp Tables. But all changes to data in Temp tables is logged to the transaction log, with all the performance implications that that entails. otoh, you can also add as amany indices or views, or triggers, or whatever else you want to a temp table exactly as you would to a ordinary table.

Table variables are a kind of short-cut in-memory table (they also use temp DB). Changes to them are not logged (this improves performance). But you can only get one index on them, (because indices cannot be created after the initial declaration statement, the only index you can create on a table variable is the one that can be included in the initial table variable declaration...

   Declare @Tab Table (myKey integer Primary Key Not Null, data varchar(20))

Because of these features, temp tables are better choice for large tables, (wide and with many rows), and/or that will undergo more than one access pattern during their lifetime, whilst table variables are best when you need a very narrow table (keys only table, or key with only one data column), which will always be accessed by that indexed key...