Difference between Temp tables and Table variables in Sql Server

Following are the difference between Temp tables and Table variables:

1. Transaction log are not recorded for table variables so they are transaction netrual or you can say they are out of scope of transaction mechanism. Whereas temp tables participate in transactions just like normal tables.

2. Table variables can not be altered it means no DDL action is allowed on them. Whereas temp tables can be altered.

3. Stored Procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

4. Unlike temp tables, table variables memory resident but not always. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

5. There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. Although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

Table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable.

No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable. Queries involving table variables don't generate parallel plans.

My Suggestions:

1. Use table variable where you want to pass table to the Sp as parameter b'coz there is no other choice.
2. Its found that table variable are slow in sql server 2005 than 2000 on similar data and circumstances, so if you have used table variables extensively in you BD and planning to migrate from sql server 2000 to 2005, make your choice carefully.
3. Table variables are OK if used in small queries and for processing small amount of data otherwise go for temp tables.
4. If your are using very complex business logic in your SP, its better using temp table than table variables.