#temp table or @table variable

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://msdn.microsoft.com/en-us/library/ms174979.aspx

In stored procedures, we often have a need to store data temporarily. There are 3 ways:

1.       local temporary table     -              #temp table

2.       global temporary table -              ##temp table

3.       Table variables                  -              @table_name

Creation:

·         #temp table is created in system database tempdb

·         ##temp table is created in system database tempdb

·         @table variable is created in memory

Scope:

·         Local temporary tables are visible to current session

·         Local temporary tables gets dropped automatically when the current procedure goes out of scope

·         Global temporary tables are visible to all sessions

·         Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.

·         Table variable are automatically cleared when procedure or function goes out of scope

Advantage:

·         #temp table - No locking is required since only one user is using #temp table

·         table variable is created in memory so performance is slightly better than #temp tables