I always ask myself, why if a have two Tables on SQL Server and i create a view like this:
CREATE VIEW vw_MyView
AS
SELECT T1.*, T2.ClientName FROM Table1 T1 inner join Table2 T2 on T1.ID_Client=T2.ID_Client
When i need to add a field to table 1, the view show Values missplaced? Values from column one are show on colunm two and so on!
This happends on every version of SQL Server i tested. Can anyone tell me why this is happening and how to fix it?
This behavior gives big problem, specially with derivated views. Thanks
After updating schema, you should refresh the view metadata for any and all views dependent on the schema:
EXEC sp_refreshview @viewName
Where the @viewName variable holds the name of the view. You can use this stored procedure in a script that can grab the views dependent on the table(s) in order to refresh them all dynamically, or you can just spin through all views (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS) and call the stored procedure on every view.
This has the added benefit of finding any views that are rendered invalid by schema changes and flagging them, as they'll error out when the SP is called.