0

I have a number of tables I wish to query with an indexed view, but those tables are spread across 3 different databases, however they are all on the same server.

The documentation states that...

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

This SO answer states that this is, in fact, possible and uses the three-part table naming to explain.

If the Microsoft documentation is correct (and I suspect it is!) do I have an alternative?

My reason for wishing to use a view is so because the query being used is somewhat slow (relatively; it has been optimised a lot) and I believe an indexed (materialised) view could help to improve performance.

Matt W
  • 11,753
  • 25
  • 118
  • 215
  • That's specifically for **indexed** views. If it's not indexed, then it can reference any number of databases. If you *must* use multiple databases, it's the underlying query you need to performance tune. – Thom A Aug 11 '21 at 15:58
  • Yes, I have stated (and just made clearer) the desire to use an indexed view. The underlying query has been performance tuned significantly already. – Matt W Aug 11 '21 at 16:03
  • 2
    As the documentation states, for *indexed views* you cannot reference other databases. There is no work around. Either don't use an indexed view, or you'll need to move your objects to be in the same database. – Thom A Aug 11 '21 at 16:04
  • If I am correct in my understanding that using a non-indexed view will not provide a performance benefit then there is no point in creating the view. – Matt W Aug 11 '21 at 16:11
  • Then you'll need to performance tune the query. – Thom A Aug 11 '21 at 16:12
  • Well, that is correct as far as a general performance question. There might be reasons for creating such a view - but that is speculation (and perhaps a bit of a code design approach). Maybe someone should reconsider the idea of spreading related information (presumably) across multiple databases. – SMor Aug 11 '21 at 16:14
  • This is a circle you cannot square. Indexed views only allow two-part naming, so everything must be in the same database. You could perhaps simulate it with triggers instead. If you want us to optimize your base query then we need the full query, table and index definitions, and the query plan – Charlieface Aug 11 '21 at 17:09
  • There is a good, solid, obvious technical reason why you cannot have an indexed view referencing multiple databases -- the engine can't maintain the consistency of such an index. Consistency is only maintainable within databases (each of which has their own transaction log), so while you can have objects referencing objects in other databases, this can only ever be on a transient basis. Maintaining an index that would somehow have to be correct for two tables in different databases is not possible. If tables can't be reshuffled, consider triggers, replication or jobs to copy data. – Jeroen Mostert Aug 11 '21 at 18:51
  • 1
    I know that your desire is to create an indexed view. But by correctly indexing the underlying tables, you should be able to achieve most if not all of what you could accomplish by indexing the view itself. – Robert Sievers Aug 11 '21 at 20:46

0 Answers0