Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Refreshing Views


Refreshing Views

Author
Message
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 185
Comments posted to this topic are about the item Refreshing Views
flavio-1155337
flavio-1155337
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
Great! This script will be **very** useful for me!! Thank you!

Just a little doubt: until now ;-) when I needed to refresh all views, I was used to create the script with all DROP and CREATE for all views, to be sure that the views are refreshed (actually: re-created) in the correct order. I'm afraid that if I refresh the views in a wrong order, I can have the same unexpected results. In other words: if I have the following dependencies chain:

view VC depend on view VB
view VB depend on view VA

and I do NOT change VC and VB, but change the structure of the VA (for example adding a new column in the middle of the SELECT column list), I'm afraid that if I refresh VC **before** refreshing VB, I can have unexpected results on VC. Instead, if I refresh (or re-create) VB before VC, everything will be ok.

I'm wrong?

Your script refreshes all views in alphabetical order... then my doubt...


Another think: I think that StpredProcedures and User defined Function do not need to be refreshed, even if underlying objects change. Is this correct?

Thank you again!
Flavio.
garyg-755799
garyg-755799
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 83
Cameron,

I am interested in the answer to Flavio's question since we have some views dependent on other views. Does it matter what order these views are refreshed?

Also, can you explain what some of the unexpected results might be? We have many non-schema bound views and are constantly changing the dependencies of our views, but we have never experienced any issues with our views.

Thanks,

Gary
flavio-1155337
flavio-1155337
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40

Also, can you explain what some of the unexpected results might be?



Hi Gary,

I've often experienced unexpected result. A typical issue, for example, occurs when you change the order of the columns on underlying views/tables of a view that use "SELECT *" (but not only with *! Also with explicit columns list!). For example if you add a new column on the underlying tables/views, expecially if you add that column in the middle and not at the end of the column list.

This is what happens to me more often.

I don't know if ther are other kind of issues... perhaps Cameron can describe to us other cases.

Bye!
Flavio.
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 185
To answer your concerns Flavio, I agree the creation order of views needs to be explicit due to dependencies if one view does depend on another, but the order of refreshing views will not matter as long as all those views have already been created prior to running of the PRefreshViews SP.

To answer your query, Stored Procedures automatically get recompiled if an underlying object gets changed. "Recomplication of a Stored Procedure occurs if an underlying table used by the stored procedure changes, and also the first time a stored procedure is run after Microsoft SQL Server is restarted." http://msdn.microsoft.com/en-us/library/ms190439.aspx (I can't find any info on functions reagrding recompiling, so I don't think it's possible or required??? correct me if i'm wrong).

To answer your question Gary, our organisation has not pin pointed one specific issue one of our customers has had with our application due to a view not being refreshed, mainly because it might not have happened at all, but also because our development team have been unaware of the need to refresh views until very recently, and wouldn't of recognised a view not being refreshed to be the cause of an unusual bug... But after some education, have been made aware of the potential issue, and believe it to be best practice to ensure our views are refreshed after any data model changes have occured. Better safe than sorry I reckon! But thanks Flavio for highlighting some of your issues you've experienced when modifying underlying objects, it shows it's worth implementing...

Thanks guys for your comments and questions.

Cameron
mstjean
mstjean
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 2542
...when I needed to refresh all views, I was used to create the script with all DROP and CREATE for all views,...

If/when you do this-- be sure you grab the permissions before dropping. Why not use ALTER instead?


Cursors are useful if you don't know SQL
flavio-1155337
flavio-1155337
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
mstjean (2/8/2010)
...when I needed to refresh all views, I was used to create the script with all DROP and CREATE for all views,...

If/when you do this-- be sure you grab the permissions before dropping. Why not use ALTER instead?


Yes, you are right! But the script wizard of SSMS allow you just to do DROP and/or CREATE script for all views, and not the ALERT... ;-) This is the reason...

And also b/c if I drop and create all views, I'm absolutely sure that the they are created in the right order.

Bye,
Flavio.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9774 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search