Will SQL Server 2000 be able to use indexes in a view over a union of a view across linked servers

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 quer开发者_开发百科ies 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?

Without knowing more details about your setup, I think your preference should be to leave the data for the two systems in place regardless of whether queries through linked servers end up using indexes. The way you have things set up sounds reasonable given the circumstance. However, I would think since you know that you need to combine the two data sets, I'd consider migrating the data from one server to the other, provided the one server can handle both transaction loads. This should provide better performance overall for your queries I would think. Again, though, it would greatly depend on what your server and network infrastructure is like.