Creating an indexed view that references an unindexed view and objects from multiple databases

Question

I have the following objects used in the following format in a view view instruction.

dbo.objects1 A INNER JOIN db2.dbo.object2 INNER JOIN db2.dbo.object3 INNER JOIN db2.dbo.object4 INNER JOIN db2.dbo.object5 

objects1 is a table from Database1 that has indexes. objects2 through objects5 are in another database , and all 4 of these objects look like No indexes .

Now I am trying to create an indexed view with all these five objects, but the SQL server does not allow me.

First mistake:

Names must be in two-part format and an object cannot reference itself.

Second error:

Cannot schema bind view 'dbo.vw_Order' because name 'db2.dbo.object2' is invalid for schema binding.

Now I was looking for these errors, and I came to the following assumptions:

• Views cannot contain objects from multiple databases in a connection request.
• To create indexes in a view, all objects in the view must either have indexes or be bound to a schema (for example, functions).

When I run the view as a query, it’s recommended in the execution plan to create an index for columns from objects in db2. Please let me know if my assumptions are true. If not, let me know how I can create a view in this situation.

Answer 1

As you have already done research, that all objects (tables / views) in the definition of an indexed view must have a TWO PART name, i.e. [Schema].[Object] , this means that all objects will be in the same database, and you cannot create an indexed view across multiple databases. Indexed views have many limitations; consider creating a stored procedure, if possible

Another error you get is the lack of the WITH SCHEMABINDING in the definition of your view. This requirement is a prerequisite for creating an indexed view, which you must use with the SCHEMABINDING option when creating an indexed view. If none of the basic schemas of tables and objects can be changed until you remove this view.

Again, I would suggest looking at stored procedures, because in your case it is impossible to create an indexed view due to all the restrictions that come with it.

Answer 2

Add an owner to the name of the view and table. How:

 Create VIEW dbo.MyView WITH SCHEMABINDING AS SELECT * From dbo.Users 

Answer 3

You cannot create indexed views using two objects from different databases.