About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, July 5, 2014

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

No comments:

Post a Comment