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][1] looking at the differences between the two object types. This also addresses your question about disk 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 write to the table in a child scope then only a `#temp` table will do
(table-valued parameters allow [readonly access][2]).
Where you do have a choice some suggestions are below (though the most reliable method is to simply test both with your specific workload).
1. If you need an index that cannot be created on a table variable then you will of course need a `#temporary` table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a `UNIQUE` or `PRIMARY KEY` constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in `CREATE INDEX`. This has been extended since to allow filtered index conditions. Indexes with `INCLUDE`-d columns or columnstore indexes are still not possible to create on table variables however.
2. 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` for large tables) and additionally subsequent inserts following a `TRUNCATE` can have better performance than those following a `DELETE` [as illustrated here][3].
3. If you will be deleting or updating a large number of rows then the temp table may well perform much better than a table variable - if it is able to use rowset sharing (see "Effects of rowset sharing" below for an example).
3. 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 (though for cached temporary tables in stored procedures [the recompilation behaviour][4] needs to be understood separately).
4. 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 (would possibly require hints to fix the plan you want).
5. 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.
6. 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 the progress of different steps in a long SQL batch.
7. When using a `#temp` table within a user transaction locks can be held longer than for table variables (potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level) 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.
8. 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][5] 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][6].
**Effects of rowset sharing**
DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);
CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);
INSERT INTO @T
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2
SET STATISTICS TIME ON
/*CPU time = 7016 ms, elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;
/*CPU time = 6234 ms, elapsed time = 7236 ms.*/
DELETE FROM @T
/* CPU time = 828 ms, elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;
/*CPU time = 672 ms, elapsed time = 980 ms.*/
DELETE FROM #T
DROP TABLE #T
[1]:
[To see links please register here]
[2]:
[To see links please register here]
[3]:
[To see links please register here]
[4]:
[To see links please register here]
[5]:
[6]:
[To see links please register here]
]
