March 19, 2009 at 12:25 pm
As I learned the hard way, SQLServer doesn't refresh views when underlying table schema changes. So if a view references columnA in tableA and you add columnB to tableA, then the view might show the value for columnB in what it labels as columnA, wreaking havoc on any application relying on SQLServer.
The hackish solution is to drop/create all views dependent on the table, but this seems like laziness on the part of SQLServer. The database knows a table has changed, and it knows what objects depend on each other, so why can't it automatically refresh the views?
I wrote a simple Python script to query sysdepends for objects dependent on a changed object, and refresh them. The only problem is that if the views were refreshed out of order, sysdepends might not contain a complete dependency graph.
Is there a better way to automatically refresh views when their dependent table/views change?
March 19, 2009 at 12:33 pm
If you create a view with schema binding, when the underlying table is changed you do get a warning that the view will be altered, which lets you know there is a view referencing the table ie:-
'Whatever' table
- Warning: The following schema-bound objects will be modified:
- View 'dbo.vwWhatever': schema binding will be removed.
Probably not what you want but better than no warning, it just gives you a reminder.
March 19, 2009 at 11:06 pm
Let me know if your ever learn the answer to your "Why?" question. I have been wondering this for 7 years now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 12:07 am
Other than schema binding we can use DDL trigger to do this task. You have to right a DD L trigger on database which checks whenever you are changing the schema of any table.
March 20, 2009 at 6:31 am
You can also just right an Agent Job that refreshes all of the views once a night or every couple of hours. Low-tech, but I used to do it on SQL 2000. Plus, on SQL 2005 you can be selective by checking for modification dates.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply