Speed comparison of temporary tables against table variables
Part four of a five-part series of blogs

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).

  1. Are Temporary Tables or Table Variables Quicker in SQL?
  2. The Example Used for this Speed Comparison
  3. Speed Comparison - Temporary Tables
  4. Speed Comparison - Table Variables (this blog)
  5. 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 - Table Variables

The SQL query to generate the table of rows for table variables is nearly identical to the one for using a temporary table:

-- create a new one

DECLARE @AdviceRows TABLE (

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

SELECT * FROM @AdviceRows

Now we've seen the two test queries, it's time to compare the results!

This blog has 0 threads Add post