Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables.
I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disc vs memory (I didn't see any significant difference in behaviour between the two).
Regarding the question in the title though as to when to use a table variable vs a local temporary table you don't always have a choice. In functions for example it is only possible to use a table variable and if you need to reference the object in a child scope then only a #temp
table will do. Where you do have a choice some suggestions are below.
- If you need an index that cannot be created implicitly through a
UNIQUE
or PRIMARY KEY
constraint then you need a #temporary
table as it is not possible to create these on table variables. (Examples of such indexes are non unique ones, filtered indexes or indexes with INCLUDE
d columns).
- If you will be repeatedly adding and deleting large numbers of rows from the table then use a
#temporary
table. That supports TRUNCATE
which is more efficient than DELETE
and additionally subsequent inserts following a TRUNCATE
can have better performance than those following a DELETE
as illustrated here.
- If the optimal plan using the table will vary dependent on data then use a
#temporary
table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data.
- If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (and possibly use of hints to fix the plan you want).
- If the source for the data inserted to the table is from a potentially expensive
SELECT
statement then consider that using a table variable will block the possibility of this using a parallel plan.
- If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging progress of different steps in a long SQL batch.
- When using a
#temp
table within a user transaction locks are held longer than for table variables and also it can prevent truncation of the tempdb
transaction log until the user transaction ends. So this might favour the use of table variables.
- Within stored routines both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for
#temporary
tables. Bob Ward points out in his tempdb
presentation that this can cause additional contention on system tables under conditions of high concurrency. Additionally when dealing with small quantities of data this can make a measurable difference to performance.