Difference between table variable & temporary table

Temporary Table
Temporary Table divided into 2
1)Local Temporary Tables
2)Global Temporary Tables

Local Temporary Tables



CREATE TABLE #Emp
(
id INT,
name VARCHAR(32)
)

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:


Global Temporary Tables

CREATE TABLE ##Emp
(
id INT,
name VARCHAR(32)
)

Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables

Table Variables

DECLARE @Emp TABLE
(
id INT,
name VARCHAR(32)
)


A table variable is created in memory, and so performs slightly better than #temp tables .Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data
The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts



Key Difference

1)Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

2)Table variables cannot be altered after they have been declared.

3)You cannot generate a table variable's column list dynamically
SELECT * INTO @tableVariable (this is not possible)



Which to Use

1)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.

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

3)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.