Hi,
Today i discuss with one of the important topic "Difference between Temporary tables and table variables"

Feature wise Difference between Table Variables and Temporary Tables

Feature Area :: Scope

1.Table Variables - Current batch

2.Temporary Tables - Current session, nested stored procedures.Global: all sessions.
Feature Area :: Usage

1.Table Variables - UDFs, Stored Procedures, Triggers, Batches.

2.Temporary Tables - Stored Procedures, Triggers, Batches.
Feature Area :: Creation

1.Table Variables - DECLARE statement only.

2.Temporary Tables - CREATE TABLE statement. SELECT INTO statement.
Feature Area :: Table name

1.Table Variables - Maximum 128 characters.

2.Temporary Tables - Maximum 116 characters.
Feature Area :: Column data types

1.Table Variables - Can use user-defined data types.Can use XML collections.

2.Temporary Tables - User-defined data types and XML collections must be in tempdb to use.
Feature Area :: Collation

1.Table Variables - String columns inherit collation from current database.

2.Temporary Tables - String columns inherit collation from tempdb database.
Feature Area :: Indexes

1.Table Variables - Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.

2.Temporary Tables - Indexes can be added after the table has been created.
Feature Area :: Constraints

1.Table Variables - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.

2.Temporary Tables - PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Feature Area :: Post-creation DDL (indexes, columns)

1.Table Variables - Statements are not allowed.

2.Temporary Tables - Statements are allowed.
Feature Area :: Insert explicit values into identity columns (SET Id IDENTITY_INSERT).

1.Table Variables - The SET IDENTITY_INSERT statement is not supported.

2.Temporary Tables - The SET IDENTITY_INSERT statement is supported.
Feature Area :: Data insertion

1.Table Variables - INSERT statement (SQL 2000: cannot use INSERT/EXEC).

2.Temporary Tables - INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Feature Area :: Truncate table

1.Table Variables - Not allowed.

2.Temporary Tables - Allowed.
Feature Area :: Destruction

1.Table Variables -Automatically at the end of the batch.

2.Temporary Tables - Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Feature Area :: Transactions

1.Table Variables - Last only for length of update against the table variable. Uses less than temporary tables.

2.Temporary Tables - Last for the length of the transaction. Uses more than table variables.
Feature Area :: Stored procedure recompilations

1.Table Variables - Not applicable.

2.Temporary Tables - Creating temp table and data inserts cause procedure recompilations.
Feature Area :: Rollbacks

1.Table Variables - Not affected (Data not rolled back).

2.Temporary Tables - Affected (Data is rolled back).
Feature Area :: Statistics

1.Table Variables - Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.

2.Temporary Tables - Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Feature Area :: Pass to stored procedures

1.Table Variables - SQL 2008 only, with predefined user-defined table type.

2.Temporary Tables - Not allowed to pass, but they are still in scope to nested procedures.
Feature Area :: Explicitly named objects (indexes, constraints).

1.Table Variables - Not allowed.

2.Temporary Tables - Allowed, but be aware of multi-user issues.
Feature Area :: Dynamic SQL

1.Table Variables - Must declare table variable inside the dynamic SQL.

2.Temporary Tables - Can use temporary tables created prior to calling the dynamic sql.

Temp Tables vs. Table Variables
Table variables can be an excellent alternative to temporary tables. They have less overhead associated with them then temporary tables do. However, they have some major limitations
as listed below. Consider using a table variable when it will contain a small amount of data, it will not be used in complex queries, and it does not need to be referenced in another SP call
or a dynamic statement. Both temporary tables and derived tables are stored in tempdb.

Table variables have the following advantages over temporary tables:
• A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.
• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
• Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Table variables have the following drawbacks as compared to temporary tables:
• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query
performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE
STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer from determining the best plan for a query, thus affecting the
performance of that query.
• Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex
queries, are modified.
• The table definition cannot be changed after the initial DECLARE statement.
• Table variables cannot be used in an INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• Since the scope of a table variable is local, you cannot reference a table variable defined in an SP from the following:
o Another SP called from the SP that this variable was defined in o A dynamic query run using EXEC() or SP_EXECUTESQL

Temporary Tables: Local vs. Global
A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can't share a local temporary table because it is local to one user session. You also can't grant or revoke permissions on the local temporary table.
A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table
disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. But you can't grant or revoke
permissions on the global temporary table because it's always available to everyone.
Local and global temporary tables differ in a subtle way. Let's look at what SQL Server Books Online (BOL) says about temporary tables. "Temporary tables are similar to permanent
tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use. The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes." The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures.
Within its execution scope, an spexecutesql system stored procedure creates and drops a local table. By definition, when the spexecutesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.

One Simple Example on follwing criteria
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:

BEGIN TRAN

declare @var table (id int, data varchar(20) )

create table #temp (id int, data varchar(20) )

insert into @var

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

insert into #temp

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

select * from #temp

select * from @var

ROLLBACK

select * from @var

if object_id('tempdb..#temp') is null

select '#temp does not exist outside the transaction'

We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.

Table variables don't participate in transactions, logging or locking. This means they're faster.
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option
You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with d