Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered indexes can be created on Temporary Tables. In Temp variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint, no other indexes can be created. 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. When working with complex queries on large tables, the lack of statistics may adversely affect the optimizers execution plan for a query, thus affecting the performance of that query.

Some Advantages of Table variables are:
•Temp Variables have a well-defined scope at the end of which they are automatically cleared off.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Table variables require less locking and logging resources because they only for the duration of an update on the table variable. Since table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

Normally, it is recommended that we can use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance.
It’s always best to test both table variables and temporary tables for a particular query or stored procedure.

Hope this helps….