No Statistic Update for Table Variable

Read Previous Post: Statistic for @Table Variable

In previous post we were checking about the presence of Statistic for @Table Variable, here will try to dig more on execution plan to see, whether the statistics which is used by optimizer is up-to-the-minute or stale.     

Again we are using same setup code for finding our conclusion. Here we are creating our table variable and inserting 5000 records in it.

set nocount on  

declare @tab_var table

(id int primary key clustered) 

declare @i int = 0    

begin tran

 while @i < 5000

 begin

  insert into @tab_var

  values (@i)  set @i=@i+1

 end

commit tran  

let set the Statistic Profile ON to see whether any statistic created for the same.

set statistics profile on

select * from @tab_var --option(recompile)

set statistics profile off

Rows Executes StmtText                                   EstimateRows

---- -------- --------------------------------------------- ----------

5000 1        select * from @tab_var                        1        

5000 1        |--Clustered Index Scan(OBJECT:(@tab_var))    1        

From the output we can see it uses the statistic, but the estimated Rows count(1) and Actual rows counts(5000) is varying with high percentage. This means the statistic is not updated with the insertion of   5k records.

But if it is so, lets set the strict recompile Option true to the query and see the estimated Row count.

set statistics profile on

select * from @tab_var option(recompile)

set statistics profile off

Rows Executes StmtText                                   EstimateRows

---- -------- --------------------------------------------- ----------

5000 1        select * from @tab_var                        5000        

5000 1        |--Clustered Index Scan(OBJECT:(@tab_var))    5000        

So here we can see the estimated number of rows increases to 5000, that means now the plan is up-to-the-minute plan.

So what conclusion we are getting here is Statistic are available for Table Variable but statics are nit updated with DML operation.

So one need to explicitly call Option (Recompile) to make the statistic update for Table variable. And that is the difference between Table variable and Temp Table.

The joy of release of ur code is more pleasurable than keeping it with you

Post Reference: Vikram Aristocratic Elfin Share