Statistic for Table Variable

There is always a trading open for all good thing, and when it comes to using Temp Table vs Table variable, you will witness super mall promoting their cases. Here in this post we won’t separate ourselves, will take the same plane to proof our finding.

Statistic for table variable; This is a point which creates muddy fight between table variable versus Temp table, Here in this post we will try to clear the ground on this difference.

Here we are creating a table variable with just a single column, And inserting around 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  

Now here we are setting 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        

So here we can see, it produces the statistic of estimated rows. But what we can see the estimated Rows count is 1 where as Actual rows count is 5000, so there is a big difference, so i can conclude this as a stale statistic for table variable.

In next post we will see the work around solution for stale statistic of table variable.

Release of Code is a paradise but release from project is a saddle to the coder! J

 

Post Reference: Vikram Aristocratic Elfin Share