in

SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

Never use table variables?

MS pushed tables variables too hard back in 2000 and developers went a little crazy with them. However, we found out that they were not the greatest thing since sliced bread especially when the result set is more that a few records and the query is even mildly complex.

The other case we hear for table variables is to avoid recompilations. This was true in SQL Server 2000. This has changed somewhat in SQL 2005 but you might not realize this by reading some web sites out there. On top of that, I cannot reproduce recompiles until much higher thresholds than what we should see per the documentation. This is a good thing in most scenarios IMO.

Based on this blog post, which is part of a great procedure cache series, we should see a recompile when 6 rows change, 500 more and at 500 + 0.20 * n more where n is the cardinality of the table.

"

After 6 modifications to an empty temporary table any stored procedure referencing that temporary table will need to be recompiled because the temporary table statistics needs to be refreshed.

The recompilation threshold for a table partly determines the frequency with which queries that refer to the table recompile. Recompilation threshold depends on the table type (permanent vs temporary), and the cardinality (number of rows in the table) when a query plan is compiled. The recompilation thresholds for all of the tables referenced in a batch are stored with the query plans of that batch.

Recompilation threshold is calculated as follows for temporary tables: n is the cardinality of the temporary table when the query plan is compiled.

If n < 6, Recompilation threshold = 6.

If 6 <= n <= 500, Recompilation threshold = 500.

If n > 500, Recompilation threshold = 500 + 0.20 * n.

"

That blog post mirrors the numbers in this must read white paper.  Both the blog and the white paper, use this example.

create procedure RowCountDemo 
as
begin
    create table #t1 (a int, b int)

    declare @i int
    set @i = 0    while (@i < 20)
    begin
       insert into #t1 values (@i, 2*@i - 50)

       select a
       from #t1
       where a < 10 or ((b > 20 or a >=100) and (a < 10000))
       group by a
 
       set @i = @i + 1
    end
end

 

Now here is the interesting part... I cannot get it to recompile. I have tried on SQL 2005 RTM, sp2 and sp2 + 3054. The initial run shows up as a recompile in a trace but subsequent runs do not. Not at @i = 100, 500 or 1000. At precisely @i = 1108, recompilation happens every time.

alter procedure RowCountDemo
as
begin
    create table #t1 (a int, b int)
 
    declare @i int
    set @i = 0    while (@i < 1108)
    begin
       insert into #t1 values (@i, 2*@i - 50)
 
       select a
       from #t1 
       where a < 10 or ((b > 20 or a >=100) and (a < 10000))
       group by a
 
       set @i = @i + 1
    end
end 

Now there may be something totally flawed in my understanding. I am sure you guys will point that out if it is the case :) But the white paper states:

"Recall that the recompilation threshold for a temporary table is 6 when the table is empty when the threshold is calculated. When RowCountDemo is executed, a "statistics changed"-related recompilation can be observed after #t1 contains exactly 6 rows. By changing the upper bound of the "while" loop, more recompilations can be observed."

If temp tables really do not cause recompilations at 6 rows, I really cannot think of a good reason to use table variables except for small sets and then only out of preference.

Technorati Tags: ,

IMMe: Jason Massie Click to IM Jason Massie

 View Jason Massie's profile on LinkedIn

Subscribe

 * cross posted from http://statisticsio.com *

Comments

 

GrumpyOldDBA said:

I've never found that the recompiles occur as published in BOL/msdn. ( 2000 or 2005 ) but never gave it much thought. Table variables are fine, as you point out, if used to hold small sets of data - I've found issues with table variables ( with largish datasets ) used in joins of several tables - but that's another matter.

January 30, 2008 1:20 PM

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at http://statisticsio.com. He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at http://linkedin.com/in/jasonmassie or jmassie@terremark.com
Powered by Community Server (Commercial Edition), by Telligent Systems