Database Reference
In-Depth Information
insert into #TT(ID)
select ID from IDs
insert into @TTV(ID)
select ID from #TT
select count(*) from #TT
select count(*) from @TTV
select count(*) from @TTV option (recompile)
As you see in Figure
12-5
, unless you are using a statement level recompile, SQL Server estimates that a table
variable has only one row. Cardinality estimation errors often progress quickly through the execution plan, and this
can lead to highly inefficient plans when table variables are used.
Figure 12-5.
Cardinality estimation for temporary tables and table variables
A statement level recompile provides the Query Optimizer with information about the total number of rows,
although no statistics are kept and the Query Optimizer knows nothing about data distribution in the table variable.
Now let's change our previous example and add a
where ID > 0
clause to all three selects. As you know, all ID
values in both tables are positive. When you run these selects, you will receive the cardinality estimations shown
in Figure
12-6
.
