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 post 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.
create procedure RowCountDemo
as
begin
create table #t1 (a int, b int)
declare @i int
set @i = 0 while (@i < 1108) --<--- Recompilations happen at >= 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.
