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
- Speed Comparison - Temporary Tables (this blog)
- 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.
Speed Comparison - Temporary Tables
Here's the code to generate our temporary table and view its results:
-- get rid of any old table
DROP TABLE IF EXISTS #AdviceRows
-- create a new one
CREATE TABLE #AdviceRows (
ID int,
OwlyAdvice varchar(100)
)
-- the number of rows to generate
DECLARE @max int = 100000
-- loop counter used and string variable
DECLARE @i int = 1
DECLARE @s varchar(100)
-- keep going till we've created enough rows
WHILE @i <=>
BEGIN
-- text to store
SET @s =
CASE
WHEN @i % 3 = 0 THEN 'This too shall pass'
WHEN @i % 3 = 1 THEN 'Look before you leap'
ELSE 'Fortune favours the brave'
END
INSERT INTO #AdviceRows (ID,OwlyAdvice)
VALUES (@i,@s)
SET @i = @i + 1
END
SELECT
a.OwlyAdvice AS Advice,
COUNT(*) AS Frequency
FROM
#AdviceRows AS a
GROUP BY
a.OwlyAdvice
ORDER BY
Frequency ASC
Next up, table variables.
- Are Temporary Tables or Table Variables Quicker in SQL?
- The Example Used for this Speed Comparison
- Speed Comparison - Temporary Tables (this blog)
- Speed Comparison - Table Variables
- Comparison of Results