The postings on this site are my own and do not represent my Employer's positions, advice or strategies.


  Saturday, December 15, 2018


bob tech
Contact Bob
VW Buses
Cook Book


Branson Vacation 2007

  bob Links
SP-100T MIG Wire Fee...
When schoolboy friends meet once again, who have not met for years. Say over what will they sit down and talk of their careers. Your "whishy-washy" wines won't do, and fiery spirits fail, for nothing blends the hearts of friends than good old English ale. - J. Caxton
Wider View Insert

IBM - AS400
Performance Counters
Service Broker
SQL Server
     Temporary Database
     SQL Mail
     DTS - Data Transformation Services
     Jobs SQL Agent
     User Management
     System Databases
     Temp Tables
          When to Use Temp Tab...
Windows OS

Insert Category

Will be added as a sub-category of, Temp Tables
When to Use Temp Tables in SQL Server

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.

  1. 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 INCLUDEd columns).
  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 and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.
  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.
  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 (and possibly use of 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 progress of different steps in a long SQL batch.
  7. 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.
  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 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.