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:.

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.

• Configuring Relocated Joins
  You must specifically enable relocated joins for each remote server involved. The remote server must be able to connect back to the local server using Component Integration Services (CIS).