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 three 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 (this blog) Speed Comparison - Table Variables Comparison of Results

Speed comparison of temporary tables against table variables

Part three 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 (this blog)
Speed Comparison - Table Variables
Comparison of Results

Posted by Andy Brown on 02 March 2017

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.

Parts of this blog
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