ALTER TABLE and VIEWS

[Naoise]

So if a view exists with something like

CREATE VIEW vwFoo
AS

SELECT tblA.*, tblB.x, tblB.y, tblB.x FROM ...


and elsewhere in business logic or stored procedure you have something like SELECT * FROM vwFoo if you actually have altered tblA then you need to rebuild the VIEW, why on earth is this?

 

[gmmastros]

why on earth is this?

I don't know, but it is clearly documented in books on line. Searching for "Create View", and scrolling to the "Remarks" section, you will find this...

When querying through a view, Microsoft® SQL Server™ checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table(s).

If a view depends on a table (or view) that was dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, and the table structure does not change from the previous base table, to replace the one dropped, the view again becomes usable. If the new table (or view) structure changes, then the view must be dropped and recreated.

-George

"the screen with the little boxes in the window." - Moron