Click here to Skip to main content
15,860,861 members
Articles / Database Development / SQL Server
Article

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

Rate me:
Please Sign up or sign in to vote.
4.81/5 (40 votes)
28 May 20074 min read 353.5K   88   21
Determining SQL Server performance for temporary tables and and table variables.

Introduction

There are three major theoretical differences between temporary tables:

SQL
CREATE table #T (…)
AND table-variables
DECLARE @T table (…)

Let's Begin

The first one is that transaction logs are not recorded for the table-variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

SQL
CREATE table #T (s varchar(128))
DECLARE @T table (s varchar(128))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @'
BEGIN transaction
  UPDATE #T set s='new value #'
  UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s             
---------------
old value #

s                
---------------
new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table-variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in INSERT/EXEC statements.

But let's compare both in terms of performance.

At first, we prepare a test table with 1 million records:

SQL
CREATE table NUM (n int primary key, s varchar(128))
GO
SET nocount on
DECLARE @n int
SET @n=1000000
WHILE @n>0 begin
  INSERT into NUM
    SELECT @n,'Value: '+convert(varchar,@n)
  SET @n=@n-1
  END
GO

Now we prepare our test procedure T1:

SQL
CREATE procedure T1
  @total int
AS
  CREATE table #T (n int, s varchar(128))
  INSERT into #T select n,s from NUM
    WHERE n%100>0 and n<=@total
  DECLARE @res varchar(128)
  SELECT @res=max(s) from NUM
    WHERE n<=@total and
      NOT exists(select * from #T
      WHERE #T.n=NUM.n)
GO

Called with a parameter, which we will vary from 10, 100, 1000, 10'000, 100'000 up to 1'000'000, it copies the given number of records into a temporary table (with some exceptions, it skips records where n is divisible by 100), and then finds a max(s) of such missing records. Of course, the more records we give, the longer the execution is.

To measure the execution time precisely, I use the code:

SQL
DECLARE @t1 datetime, @n int
SET @t1=getdate()
SET @n=100 – (**)
WHILE @n>0 begin
  EXEC T1 1000 – (*)
  SET @n=@n-1 end
SELECT datediff(ms,@t1,getdate())
GO

(*) is a parameter to our procedure, it is varied from 10 to 1'000'000 (**) if an execution time is too short, I repeat the same loop 10 or 100 times. I run the code several times to get a result of a 'warm' execution.

The results can be found in Table 1 (see below).

Now let's try to improve our stored procedure by adding a primary key to the temporary table:

SQL
CREATE procedure T2
  @total int
AS
  CREATE table #T (n int primary key, s varchar(128))
  INSERT into #T select n,s from NUM
    WHERE n%100>0 and n<=@total
  DECLARE @res varchar(128)
  SELECT @res=max(s) from NUM
    WHERE n<=@total and
      NOT exists(select * from #T
      WHERE #T.n=NUM.n)
GO

Then, let's create a third one. With a clustered index, it works much better. But let's create the index AFTER we insert data into the temporary table – usually, it is better:

SQL
CREATE procedure T3
  @total int
AS
  CREATE table #T (n int, s varchar(128))
  INSERT into #T select n,s from NUM
    WHERE n%100>0 and n<=@total
  CREATE clustered index Tind on #T (n)
  DECLARE @res varchar(128)
  SELECT @res=max(s) from NUM
    WHERE n<=@total and
      NOT exists(select * from #T
      WHERE #T.n=NUM.n)
GO

Surprise! It not only takes longer for the big amounts of data; merely adding 10 records take an additional 13 milliseconds. The problem is that 'create index' statements force SQL Server to recompile stored procedures, and slows down the execution significantly.

Now let's try the same using table-variables:

SQL
CREARE procedure V1
  @total int
AS
  DECLARE @V table (n int, s varchar(128))
  INSERT into @V select n,s from NUM
    WHERE n%100>0 and n<=@total
  DECLARE @res varchar(128)
  SELECT @res=max(s) from NUM
    WHERE n<=@total and
      NOT exists(select * from @V V
      WHERE V.n=NUM.n)
GO

To our surprise, this version is not significantly faster than the version with the temporary table. This is a result of a special optimization SQL Server has for the create table #T statements in the very beginning of a stored procedure. For the whole range of values, V1 works better or the same as T1.

Now let's try the same with a primary key:

SQL
CREATE procedure V2
  @total int
AS
  DECLARE @V table (n int primary key, s varchar(128))
  INSERT into @V select n,s from NUM
    WHERE n%100>0 and n<=@total
  DECLARE @res varchar(128)
  SELECT @res=max(s) from NUM
    WHERE n<=@total and
      NOT exists(select * from @V V
      WHEREre V.n=NUM.n)
GO

The result is much better, but T2 outruns this version.

Table 1, using SQL Server 2000, time in ms

Records
T1T2T3V1V2
100.7113.50.60.8
1001.21.714.21.21.3
10007.15.52775.3
100007257827148
100000883480580840510
1000000450566090152202024012010

But the real shock is when you try the same on SQL Server 2005:

Table 2

N
T1T2T3V1V2
100.50.55.30.20.2
10021.26.461.82.5
10009.38.513.5168140
1000067.479.271.31713313910
100000700794659

Too long!

Too long!

10000001055686736440

Too long!

Too long!

In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In four cases, I even gave up waiting.

Conclusion

  1. There is no universal rule of when and where to use temporary tables or table variables. Try them both and experiment.
  2. In your tests, verify both sides of the spectrum – small amount/number of records and the huge data sets.
  3. Be careful with migrating to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL Server 2005!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Russian Federation Russian Federation
Born in 1968, Dmitry Tsuranoff is SQL professional who addresses issues and problems from the perspective of both a database developer and a DBA. He has worked in the United States, France and Russia. Currently, he is employed as Systems Architect and Team Manager at Lakeside Technologies, developer of high-performance Lakeside SQL Server Tools.

For more information, please visit http://www.lakesidesql.com.

Comments and Discussions

 
QuestionPassing Table variable in EXEC statement Pin
Member 796298310-Nov-15 3:01
Member 796298310-Nov-15 3:01 
QuestionOperations on table variables ARE logged Pin
MartinSmith100000016-Feb-13 3:12
MartinSmith100000016-Feb-13 3:12 
GeneralMy vote of 5 Pin
Anish Jain29-Nov-12 8:05
professionalAnish Jain29-Nov-12 8:05 
GeneralSome notes on table variables and temp tables Pin
ncarey6-Jun-07 10:51
ncarey6-Jun-07 10:51 
GeneralRe: Some notes on table variables and temp tables Pin
Dmitry Tsuranoff6-Jun-07 21:50
Dmitry Tsuranoff6-Jun-07 21:50 
GeneralRe: Some notes on table variables and temp tables Pin
Paladin_428-Jun-07 3:36
Paladin_428-Jun-07 3:36 
GeneralAmen Pin
mike good6-Jun-07 7:12
mike good6-Jun-07 7:12 
GeneralHmmmm... Pin
reshi9995-Jun-07 22:20
reshi9995-Jun-07 22:20 
GeneralKeep table vars small Pin
SimulationofSai4-Jun-07 23:30
SimulationofSai4-Jun-07 23:30 
QuestionThanks - but which service packs? Pin
Pete Appleton4-Jun-07 22:54
Pete Appleton4-Jun-07 22:54 
Thanks for the very informative & useful article - got my 5. Just one question; which service packs were applied to the DB's used for the comparison? One of the major fixes for 2K5 was meant to be performance, so I'm interested to know if the service pack was applied.

--
What's a signature?

AnswerRe: Thanks - but which service packs? Pin
Dmitry Tsuranoff5-Jun-07 1:03
Dmitry Tsuranoff5-Jun-07 1:03 
GeneralRe: Thanks - but which service packs? Pin
Pete Appleton5-Jun-07 4:25
Pete Appleton5-Jun-07 4:25 
GeneralCheck the verbose traces of your samples Pin
Vlad Vissoultchev28-May-07 11:47
Vlad Vissoultchev28-May-07 11:47 
GeneralRe: Check the verbose traces of your samples Pin
TJoe28-May-07 16:55
TJoe28-May-07 16:55 
GeneralRe: Check the verbose traces of your samples Pin
Dmitry Tsuranoff29-May-07 0:03
Dmitry Tsuranoff29-May-07 0:03 
GeneralRe: Check the verbose traces of your samples Pin
Vlad Vissoultchev29-May-07 1:41
Vlad Vissoultchev29-May-07 1:41 
GeneralRe: Check the verbose traces of your samples Pin
Dmitry Tsuranoff29-May-07 1:50
Dmitry Tsuranoff29-May-07 1:50 
GeneralRe: Check the verbose traces of your samples Pin
Vlad Vissoultchev30-May-07 0:28
Vlad Vissoultchev30-May-07 0:28 
GeneralRe: Check the verbose traces of your samples Pin
fletchsod6-May-08 5:01
fletchsod6-May-08 5:01 
GeneralRe: Check the verbose traces of your samples Pin
Suman Kumar1-Nov-08 5:47
Suman Kumar1-Nov-08 5:47 
GeneralThank you Pin
merlin98128-May-07 10:59
professionalmerlin98128-May-07 10:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.