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 five 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? The Example Used for this Speed Comparison Speed Comparison - Temporary Tables Speed Comparison - Table Variables Comparison of Results (this blog)

Speed comparison of temporary tables against table variables

Part five 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?
The Example Used for this Speed Comparison
Speed Comparison - Temporary Tables
Speed Comparison - Table Variables
Comparison of Results (this blog)

Posted by Andy Brown on 02 March 2017

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.

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