Why do SQL Server Views needs to be refreshed every once in a while

Question

I'm doing quite a lot of backend developing the past few days, and as we're working with central database scripts, I just write plain old SQL upgrade scripts, and execute them to the database. Well fine and all, but why do 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 it's view to go refresh himself after editing the view through a script?

Answer 1

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

Answer 2

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

And in combination with ALTER VIEW, not the designer