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

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 many 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 characteristics, 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...

Answer 2

This is a pretty good reference on the different temp tables

Temp Tables vs Variables

Answer 3

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 this topic.

Answer 4

1. Table variables have a well defined scope. They will be cleared automatically at the end of the batch (i.e current batch of statements) where as temporary table will be visible to current session and nested stored procedures. Global Temporary table will be visible to the all the sessions.

2. Table variables are created using Declare statement. We can't create table variable using statement

   select * into @tableVariableName
   

   But we can create temporary table using Create table statement as well as statement

   select * into #tempTableName
   

3. In SQL Server 2008 onwards we can pass the table variable as a parameter to the stored procedures. But we can't pass the temporary table as a parameter to the stored procedure.

4. We can use the table variable inside the UDF (user defined function) but we can't use the temporary table inside the UDF.