This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Speed comparison of temporary tables against table variables Part one of a five-part series of blogs
When you are 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). Are Temporary Tables or Table Variables Quicker in SQL? (this blog) The Example Used for this Speed Comparison Speed Comparison - Temporary Tables Speed Comparison - Table Variables Comparison of Results

Speed comparison of temporary tables against table variables

Part one of a five-part series of blogs

When you are 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).

Are Temporary Tables or Table Variables Quicker in SQL? (this blog)
The Example Used for this Speed Comparison
Speed Comparison - Temporary Tables
Speed Comparison - Table Variables
Comparison of Results

Posted by Andy Brown on 02 March 2017

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 online tutorials (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:

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.

Parts of this blog
Are Temporary Tables or Table Variables Quicker in SQL? (this blog) The Example Used for this Speed Comparison Speed Comparison - Temporary Tables Speed Comparison - Table Variables Comparison of Results