Relocated Joins

Joins between local and remote tables can be relocated to a remote server. The remote system executes the join using a dynamically created proxy table referring back to the local table, avoiding a significant amount of network traffic.

This is an example of how to use relocated join:.

A join between local table ls1 and remote table rl1 results in this query being sent to the remote server:
select a,b,c 
from localserver.testdb.dbo.ls1 t1, rl1 t2 
where t1.a = t2.a

The statement sent to the remote server contains a fully qualified reference back to the local table on the local system. The remote server either dynamically creates a temporary proxy table definition for this table, or uses an existing proxy table with a matching mapping. The remote server then executes the join and returns the result set back to the local server.

See the Performance and Tuning Series: Query Processing and Abstract Plans.