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

LifeAsBob

  Thursday, March 28, 2024
Wider View Login

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
SQL Server
     Temporary Database
     Views
     Backup
     SQL Mail
     DTS - Data Transformation Services
     Memory
     Security
     Excel
     Jobs SQL Agent
     MSDE
     Install
     DBCC
     User Management
     System Databases
     BCV
     Deadlocks
     Temp Tables
          When to Use Temp Tab...
Windows OS




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.