Refreshing Views

Question

Refreshing Views

cameron.wells

SSC-Addicted

Points: 460
More actions
January 15, 2010 at 1:34 am

#219256

Comments posted to this topic are about the item Refreshing Views

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

flavio-1155337 SSC Enthusiast Points: 178 More actions · Answer 1

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 SSC Veteran Points: 228 More actions · Answer 2

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 SSC Enthusiast Points: 178 More actions · Answer 3

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 SSC-Addicted Points: 460 More actions · Answer 4

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 Hall of Fame Points: 3310 More actions · Answer 5

...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 SSC Enthusiast Points: 178 More actions · Answer 6

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2016 at 1:13 pm

#1880344

Thanks for the script.