How to create schema bound, cross-database view in SQL Server

Question

I am attempting to create an indexed view on SQL Server 2008. I have a master database in which I cannot make any changes to (in terms of adding tables, views, etc.). However, I need to create some different views for various reasons that need to work with live data.

I have created a new database along side my master database so I can create views there. I am able to create views just fine, but I want to index some of the larger views. However, when I try to create a schema bound view cross-database, I receive the following error:

Cannot schema bind view 'dbo.Divisions' because name 'master.dbo.hbs_fsdv' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Since I am going cross-database with the views, I have to reference the name in three-part format.

My creation statement for the view:

CREATE VIEW dbo.Divisions WITH SCHEMABINDING AS
SELECT master.dbo.hbs_fsdv.seq_              AS DivisionID, 
       master.dbo.hbs_fsdv.fs_division_desc_ AS Description 
  FROM master.dbo.hbs_fsdv

How can I create an indexed cross-database view in SQL Server?

Answer 1

Plain and simple. You can't. From the MSDN page:

The view must reference only base tables that are in the same database as the view.

https://msdn.microsoft.com/en-us/library/ms191432.aspx

Answer 2

Although (per the docs) it cannot be done directly with a simple SQL statement, this use case is very common and has a solution.

The architecture would have to involve caching the remote tables into your centralized database, and building the indexed view on top of them.

Some good notes on this can be found here:

What is the best way to cache a table from a (SQL) linked server view?

and

https://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/