So here the scneario.
We have a database of read only records that contains about 3 million rows. This database is used by all of our application databases for data lookup.
We placed this database on a new server and used a synonym to point to it in the local database. The synonym was pointing to a view on the remote server. This allowed us to update these 3 million rows during the day and swap the synonym pointer to the new table without effecting our clients searches.
If I query the synonym by itself the results are as expected filtered at the remote database but if I union this data with local look up data as required the remote query returns all 3 million rows to the local server and then the data is filtered.
This also happened if we dropped the sysnonym and used a view to point to the view.
We had to point a view directly at the remote table in order to accomplish this request without returning all rows.
I am really interested in why the Union operator caused this effect.
Any thoughts?