Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Refreshing Views Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 1:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
Comments posted to this topic are about the item Refreshing Views
Post #848122
Posted Wednesday, February 03, 2010 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 2:35 AM
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.
Post #858588
Posted Wednesday, February 03, 2010 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 21, 2013 2:17 PM
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
Post #858755
Posted Wednesday, February 03, 2010 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 2:35 AM
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.
Post #858780
Posted Wednesday, February 03, 2010 6:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
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
Post #859218
Posted Monday, February 08, 2010 3:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:30 PM
Points: 436, Visits: 2,198
...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
Post #862108
Posted Tuesday, February 09, 2010 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 2:35 AM
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.
Post #862253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse