Friday, March 27, 2015

Table Variable vs Temp Table, which one to prefer and when

Yesterday I came accross a situation where one of the stored procedure written by my collegue started misbehaving all of a sudden, It was becomming non responsive to a point where we were inserting a record into a table variable from a CTE query.

On further analysis I found that everything was okay with the query untill the point it tries to insert records into the table variable. I replaced that table variable with a temp table and everything went smoothly. That's when I thought to make a writeup on this topic for which one to use, and when, cause both are have some pross and cons.

  • For larger datasets use temp table. If your number of records are going to be greater than 100 use temp table, micrsoft recommends
  • Indexes cannot be created explicitly on table variables and no statistics are kept on table variable, so use temp tables if want to improve performance by indexes and supported statistics
  • Since table variables are still a varibales they do not participate in transactions, you can use them at a place where you want to log progress of large SQL batch
  • Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead
  • On the other hand when using fewer number of records use table variable instead cause it create less overhead on the database, and may need less or no in case they are stored in memory, because of being smaller in size. 
  • If you are frequently adding and deleting records from it then use temporay tables cause they support truncate command which has better performance than delete
  • 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.
  • 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
  • A table varibale cannot be altered after creation, however you you can alter the structure of a temp table by using an alter table command    

1 comment: