Speed comparison of temporary tables against table variables
Part five of a five-part series of blogs

When you're programming in SQL, you often have the choice of using table variables or temporary tables. This blog considers whether one method is substantially quicker than the other (spoiler alert: not really).

  1. Are Temporary Tables or Table Variables Quicker in SQL?
  2. The Example Used for this Speed Comparison
  3. Speed Comparison - Temporary Tables
  4. Speed Comparison - Table Variables
  5. Comparison of Results (this blog)

Posted by Andy Brown on 02 March 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Comparison of Results

Here are the results from running the two queries (the times shown are in seconds, as recorded in SQL Server Management Studio):

  Temporary tables Table variables
100,000 rows 2 seconds 2 seconds
1,000,000 rows 14 seconds 18 seconds
3,000,000 rows 60 seconds 72 seconds
5,000,000 rows 190 seconds 208 seconds

What this shows is that it doesn't really matter which method you use - the results are similar.

My personal preference is to use table variables - I like the fact that you have to define their structure at the start, and you know they won't exist when your query finishes.

This blog has 0 threads Add post