For various corporate and historical and replicating reasons, I have multiple databases that contain similar-structured tables, holding the main production data used as a source for other home-made applications and reports.
In a particular application, I created views that UNION all theses similar tables, adding a column to know the source of each row. The problem I have is that this view, as explained in Books Online and various other posts in various forums, cannot have any indexes (because of the UNION and the fact that the source is in another database, thus it cannot be SCHEMABOUND), and so is performing quite poorly.
The question is: if I need the data as live as possible, what other choices do I have to increase performance somewhat?
- Database A, table X
- Database B, table X
- Database C, table X
Application S (in it's own database), containing view:
CREATE VIEW vw_X AS SELECT 'A' as source, * FROM A..X UNION SELECT 'B' as source, * FROM B..X UNION SELECT 'C' as source, * FROM C..X
I am using SQL Server 2012, if there are server-specific elements that could help me.
I am lucky, for now, all databases are on the same server, so I am not using linked servers. But application S can be using Synonyms to reference the various "table X" if needed.
Yes, I did a lot of searches. I got a lot of "it's impossible", but came up empty solution-wise, other than the idea of copying the tables locally on a schedule, something hard for us considering the amount of data that would need to be copied over, and how often it would need to be copied. I am looking for alternative ideas that could lurk in the corners.
The option of turning the view into a stored procedure could be considered.
Thanks in advance