Speed comparison of temporary tables against table variables
Part one 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? (this blog)
  2. The Example Used for this Speed Comparison
  3. Speed Comparison - Temporary Tables
  4. Speed Comparison - Table Variables
  5. Comparison of Results

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.

Are Temporary Tables or Table Variables Quicker in SQL?

This blog assumes that you already know what temporary tables and table variables are (if you don't, you could do worse than read our blogs (here or here) or watch our video tutorials (here or here) on the subject - or go on our advanced SQL course).

Here are the contents of the rest of this blog:

Subject More details
The example used Explaining the methodology behind this speed comparison
Temporary tables The SQL code to generate and test a temporary table
Table variables The same code to generate and test a table variable
Conclusion The conclusion of the tests, giving relative speeds

I'm running the tests in SQL Server 2016 under Windows 10 Professional, using this laptop:

Laptop configuration

The configuration of the laptop running these tests.

Let's start with the example I've used, which you can recreate in your own instance of SQL Server.

This blog has 0 threads Add post