SQL 创建引用非索引视图和来自多个数据库的对象的索引视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22873019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating Indexed View which references a Non Indexed View and objects from multiple DB's
提问by Sanket J
I have the following objects used in the following format in a create view statement.
我在创建视图语句中以以下格式使用了以下对象。
dbo.objects1 A
INNER JOIN db2.dbo.object2
INNER JOIN db2.dbo.object3
INNER JOIN db2.dbo.object4
INNER JOIN db2.dbo.object5
The objects1is a table from Database1which has Indexes. The objects2 through objects5are in another databaseand all these 4 objects are view with No indexes.
该objects1是从表中Database1具有索引。该objects2 through objects5是另一种database，所有这4个对象视图与No indexes。
Now I am trying to create an indexed view with all the above five objects but SQL server is not allowing me.
现在我正在尝试使用上述所有五个对象创建索引视图，但 SQL Server 不允许我这样做。
First error is:
第一个错误是：
Names must be in two-part format and an object cannot reference itself.
Names must be in two-part format and an object cannot reference itself.
Second error is:
第二个错误是：
Cannot schema bind view 'dbo.vw_Order' because name 'db2.dbo.object2' is invalid for schema binding.
Cannot schema bind view 'dbo.vw_Order' because name 'db2.dbo.object2' is invalid for schema binding.
Now I googled these errors and I came to following assumptions:
现在我用谷歌搜索了这些错误，并得出了以下假设：
- Views cannot contain objects from multiple databases in a join query.
- In order to create indexes on a view, all the objects in the view should have either indexes or should be schema binded (like Functions).
- 视图不能在连接查询中包含来自多个数据库的对象。
- 为了在视图上创建索引，视图中的所有对象都应该有索引或应该是模式绑定的（如函数）。
When I run the view like a query, Execution Plan is recommending me to create an index on columns from objects in db2. Please let me know if my assumptions are correct. If not, please let me know a way as to how I can create a view in this situation.
当我像查询一样运行视图时，执行计划建议我在 db2 中对象的列上创建索引。请让我知道我的假设是否正确。如果没有，请让我知道如何在这种情况下创建视图。
回答by M.Ali
As you have already done the research that all the objects(tables/View) in a definition of an indexed view must have TWO PARTname i.e [Schema].[Object]it means all the objects will be in one database and you cannot create indexed view across multiple databases. Indexed views come with a lot of limitations, consider creating a stored procedure if possible
正如您已经完成的研究，索引视图定义中的所有对象（表/视图）都必须具有TWO PART名称，即[Schema].[Object]这意味着所有对象都将位于一个数据库中，并且您不能跨多个数据库创建索引视图。索引视图有很多限制，如果可能，考虑创建一个存储过程
Other error that you are getting is you are missing WITH SCHEMABINDINGoption in your view's definition. It is a must requirement for creating an indexed view that you must use the WITH SCHEMABINDING option when creating indexed view i.e none of the underlying tables/objects schema can be changed until you drop this view.
您遇到的其他错误是您WITH SCHEMABINDING在视图定义中缺少选项。创建索引视图必须在创建索引视图时使用 WITH SCHEMABINDING 选项，即在删除此视图之前，不能更改任何基础表/对象模式。
Again I would suggest to look into stored procedures as it seems impossible in your case to create an Indexed View because of all the limitation that come with it.
我再次建议研究存储过程，因为在您的情况下似乎不可能创建索引视图，因为它存在所有限制。
回答by Cameron Castillo
Add the owner to the view name and tables. Like:
将所有者添加到视图名称和表中。喜欢：
Create VIEW dbo.MyView
WITH SCHEMABINDING
AS
SELECT * From dbo.Users
回答by Ritesh kumar
You cannot create indexed view using two objects of different databases.
不能使用不同数据库的两个对象创建索引视图。
