13

Why do I have to write 'refresh view' scripts, and execute them every time I add or edit some fields to a view?

SQL Server understands that it needs to refresh the view when editing it in the fancy view-edit windows in Management Studio, so why can't it just tell its view to go refresh itself after editing the view through a script?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Jan Jongboom
  • 26,598
  • 9
  • 83
  • 120

3 Answers3

12

Views need to be refreshed if the underlying tables change at all. That can change the datatypes of the view's columns or rearrange its indexes. Therefore, it needs to know. Otherwise, you'd run a query against it, and it'd blow up pretty quickly.

You shouldn't have to run sp_refreshview for altering a view. Only for altering its underlying tables.

Also, please do not taunt happy fun ball.

Edit: Just ran this code (in succession) to attempt to reproduce your problem. I was, unfortunately, unable to, as it worked as expected (SQL Server 2008):

create view MyView
as
select ProductKey, ProductID, ProductName, Price
from dbo.Products

select v.* from MyView v

alter view MyView
as
select ProductKey, ProductID, ProductName, Price*100 as MyPrice
from dbo. Products

select v.* from MyView v
Eric
  • 92,005
  • 12
  • 114
  • 115
  • 1
    But this behaviour also happens when I have something like SELECT b.* FROM b. After adding columns to b, the view tries to display the old columns, even though they're not hardcoded. – Jan Jongboom Aug 13 '09 at 15:55
  • 2
    Eric's comments still hold. The metadata for the view needs to be refreshed whenever the schema for the underlying tables is changed, even if the code for the view would otherwise remain correct. – TimothyAWiseman Aug 13 '09 at 16:01
6

Use WITH SCHEMABINDING in the view definition to remove the need for any refreshes

And in combination with ALTER VIEW, not the designer

Edit, Jul 2012, from link above. My bold

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 4
    I hesitated to suggest this. `WITH SCHEMABINDING` *only* works if the tables are all in the same database on the same server. – Eric Aug 13 '09 at 15:53
  • 1
    Using with schemabinding would remove the need for any refreshes because it prevents the underlying tables from being changed. The best this would do if you have to change the underlying tables is give you a reminder that you need to update the view afterwards as you remove the with schemabinding to make the change. – TimothyAWiseman Aug 13 '09 at 16:02
  • 1
    I don't see how preventing the change at all is equivalent to removing the need for refreshes? Unless I am mistaken that WITH SCHEMABINDING will prevent the underlying tables from being changed? – ErikE Jul 23 '12 at 22:04
2

I had the same problem with table changes. The real solution is a DDL trigger for alter table:

Create Trigger RefreshViewTrigger On Database FOr Alter_Table As
Declare @tname as nvarchar(256), @sql nvarchar(400);
Select @tname = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)')  + '.' +  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)');
Declare k cursor For Select distinct 'sp_refreshview ''' + o.name + '''' sql
    From sys.objects o Join sys.sql_expression_dependencies s On o.object_id = s.referencing_id   
    Where o.type = 'V' AND s.referenced_id = Object_id(@tname);  
Open k
Fetch Next from k into @sql
While @@FETCH_STATUS = 0
Begin
    Print( @sql )
    EXEC( @sql )
    Fetch Next from k into @sql
End
Close k
Deallocate k
Go

I works on 2008 R2, maybe even earlier versions.

Peter Krassoi
  • 571
  • 3
  • 11