I always ask myself, why if a have two Tables on SQL Server and i create a view like this:
我总是问自己,为什么如果在SQL Server上有两个表,我创建一个这样的视图:
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!
当我需要向表1添加字段时,视图显示值未命中?第一列的值显示在colunm 2上,依此类推!
This happends on every version of SQL Server i tested. Can anyone tell me why this is happening and how to fix it?
这发生在我测试的每个版本的SQL Server上。任何人都可以告诉我为什么会发生这种情况以及如何解决这个问题?
This behavior gives big problem, specially with derivated views. Thanks
这种行为给出了很大的问题,特别是对于派生的视图。谢谢
2 个解决方案
#1
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.
@viewName变量保存视图名称的位置。您可以在脚本中使用此存储过程,该脚本可以获取依赖于表的视图,以便动态刷新它们,或者您可以旋转所有视图(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)并调用存储过程在每个观点上。
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.
这样做的另一个好处是可以找到任何因架构更改而无效的视图并标记它们,因为它们在调用SP时会出错。
#2
The accepted answer is OK (I voted up too) for fixing that issue after changing the tables. For my environments I prefer to use WITH SCHEMABINDING (see https://msdn.microsoft.com/en-us/library/ms187956.aspx) for creation of the views. That leads to not being able to use the * in the select of the view as has been discussed here: Why is using '*' to build a view bad? and of course to not being able to change the columns of the table without changing (in most cases: dropping, recreating) the view
接受的答案是肯定的(我也投票了)在更改表格后解决了这个问题。对于我的环境,我更喜欢使用WITH SCHEMABINDING(请参阅https://msdn.microsoft.com/en-us/library/ms187956.aspx)来创建视图。这导致无法在选择视图中使用*,如此处所讨论的:为什么使用'*'构建视图不好?当然,如果不改变(在大多数情况下:删除,重新创建)视图,就无法更改表的列
That is of course more cumbersome when changing tables, but that way you dont get runtime errors when forgetting to call EXEC sp_refreshview after a table change (for example column drop) because you are forced by the database to adapt the view as well.
在更改表时,这当然更麻烦,但是这样在忘记在表更改后调用EXEC sp_refreshview(例如列删除)时就不会出现运行时错误,因为数据库也强制您调整视图。
#1
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.
@viewName变量保存视图名称的位置。您可以在脚本中使用此存储过程,该脚本可以获取依赖于表的视图,以便动态刷新它们,或者您可以旋转所有视图(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)并调用存储过程在每个观点上。
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.
这样做的另一个好处是可以找到任何因架构更改而无效的视图并标记它们,因为它们在调用SP时会出错。
#2
The accepted answer is OK (I voted up too) for fixing that issue after changing the tables. For my environments I prefer to use WITH SCHEMABINDING (see https://msdn.microsoft.com/en-us/library/ms187956.aspx) for creation of the views. That leads to not being able to use the * in the select of the view as has been discussed here: Why is using '*' to build a view bad? and of course to not being able to change the columns of the table without changing (in most cases: dropping, recreating) the view
接受的答案是肯定的(我也投票了)在更改表格后解决了这个问题。对于我的环境,我更喜欢使用WITH SCHEMABINDING(请参阅https://msdn.microsoft.com/en-us/library/ms187956.aspx)来创建视图。这导致无法在选择视图中使用*,如此处所讨论的:为什么使用'*'构建视图不好?当然,如果不改变(在大多数情况下:删除,重新创建)视图,就无法更改表的列
That is of course more cumbersome when changing tables, but that way you dont get runtime errors when forgetting to call EXEC sp_refreshview after a table change (for example column drop) because you are forced by the database to adapt the view as well.
在更改表时,这当然更麻烦,但是这样在忘记在表更改后调用EXEC sp_refreshview(例如列删除)时就不会出现运行时错误,因为数据库也强制您调整视图。