I have the following scenario:
2 DB servers (Linked to each other)
DB1 has a (large) table with transaction records DB2 has a (not quite as large yet) table with transaction records (of a similar nature but without some of the data as it is a different system) There are a bunch of reports that pull records out of the transaction table in DB1 and joins them up with lots of tables from DB1.
I can use data in DB1 to make the data from DB2 look like data in DB1.
If I create a view in DB1 that pulls the transaction data from DB2 and joins it up with data from DB1 to have the same columns as the transaction data in DB1, and then create another view that union-alls the data from DB1 and the first view, will queries against this second view still take advantage of any of the indexes on the transaction data in DB1 and DB2?
Or are there better ways of retro fitting data from a new system to an old system so that the old reports can report on the new data?