I'm learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table.
But sometimes, if there are too many records in a table variable that can not be contained in memory, the table variable will be put on disk like the temp table.
But I don't know what the "too many records" is. 100,000 records? or 1000,000 records? How can I know if a table variable I'm using is in memory or is on disk? Is there any function or tool in SQL Server 2005 to measure the scale of the table variable or letting me know when the table variable is put on disk from memory?
Best Answer
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 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).Where you do have a choice some suggestions are below (though the most reliable method is to simply test both with your specific workload).
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 aUNIQUE
orPRIMARY KEY
constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available inCREATE INDEX
. This has been extended since to allow filtered index conditions. Indexes withINCLUDE
-d columns or columnstore indexes are still not possible to create on table variables however.If you will be repeatedly adding and deleting large numbers of rows from the table then use a
#temporary
table. That supportsTRUNCATE
(which is more efficient thanDELETE
for large tables) and additionally subsequent inserts following aTRUNCATE
can have better performance than those following aDELETE
as illustrated here.#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 needs to be understood separately).SELECT
statement then consider that using a table variable will block the possibility of this using a parallel plan.#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 thetempdb
transaction log until the user transaction ends. So this might favour the use of table variables.#temporary
tables. Bob Ward points out in histempdb
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.Effects of rowset sharing