Creating Indexed View which references a Non Indexed View and objects from multiple DB's

Question

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 objects1 is a table from Database1 which has Indexes. The objects2 through objects5 are in another database and all these 4 objects are view with No indexes.

Now I am trying to create an indexed view with all the above five objects but SQL server is not allowing me.

First error is:

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.

Now I googled these errors and I came to following assumptions:

1. Views cannot contain objects from multiple databases in a join query.
2. 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.

Answer 1

As you have already done the research that all the objects(tables/View) in a definition of an indexed view must have TWO PART name 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

Other error that you are getting is you are missing WITH SCHEMABINDING option 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.

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.