BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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 (this blog)
- Speed Comparison - Temporary Tables
- Speed Comparison - Table Variables
- 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.
The Example Used for this Speed Comparison
What I've chosen to do is to generate a table containing Wise Owl life advice:
The advice becomes a bit repetitive, since it repeats after 3 rows. The idea is to ensure we have a couple of reasonably authentic columns of data.
The SQL queries used each include a variable setting the number of rows to be generated:
-- the number of rows to generate
DECLARE @max int = 100000
I'll try running the queries with this variable set to 100,000, 1 million, 3 million and 5 million. The query should count the number of rows for each type of advice to produce something like this:
Given the advice will be randomly generated in equal amounts, the frequencies should be similar!
Let's start with temporary tables ...
- Are Temporary Tables or Table Variables Quicker in SQL?
- The Example Used for this Speed Comparison (this blog)
- Speed Comparison - Temporary Tables
- Speed Comparison - Table Variables
- Comparison of Results