BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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).
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.