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

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

Printing next 5 Sunday, Part-3

Read Prev Post   : Printing next 5 Sunday, Part-1

                         : Printing next 5 Sunday, Part-2

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

This is again another approach to find the next 5 Sunday, here we removed the CTE which we used in the last post, and replace it with Rank function.

declare @dt  datetime

declare @howMany integer =5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

select top (@howMany)  dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt) as weekdays,

DATENAME(dw, DATEADD(d, 7 * (cast(ROW_NUMBER() over (order by name) as int) - 1), @dt)) as Weekdays

from sys.objects

date                    Weekdays

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

2014-07-06 00:00:00.000 Sunday

2014-07-13 00:00:00.000 Sunday

2014-07-20 00:00:00.000 Sunday

2014-07-27 00:00:00.000 Sunday

2014-08-03 00:00:00.000 Sunday

Let’s dissect the code and understand each statement.

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

Lets divide this statement further more

cast(ROW_NUMBER() over (order by name) as int) – 1

This will give list of numbers starts from 0,1,2,3…….

Here below line will multiply 7*(0,1,2,3….) and add the number of days in multiple of 7 to the @dt date

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

So if the @dt is 2014-07-06

In first Iteration

select dateadd(d,7*(0),'2014-07-06')

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

2014-07-06 00:00:00.000

In second Iteration

select dateadd(d,7*(1),'2014-07-06')

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

2014-07-13 00:00:00.000

I am a coder, I have given my life to you, give me smile now! J

 

Post Reference: Vikram Aristocratic Elfin Share