Heterogeneous Query Internals

when executing a query joining a local table to a linked server's table, or when querying the linked server's table directly through a local session, does the engine "pass through" to the linked engine the query portion that can be addressed by that server?

for example. . .

select local.master, linked.detail
from mydb.dbo.mytable local inner join linked.yourdb.dbo.yourtable linked
on local.id = linked.id
where linked.lastname = 'smith'

is the engine smart enough pass the " linked.lastname = 'smith' " to the linked engine before doing the join?

or

select linked.*
from linked.yourdb.dbo.yourtable linked
where linked.lastname = 'smith'

does the engine pass the " linked.lastname = 'smith' " to the linked server for it to process?

any reference links would be helpful.

Blair,

Sometimes yes, sometimes no. You should be able to see the remote query by generating the estimated execution plan in SQL Server Management Studio, then hovering the mouse cursor over the remote query operator. The query processor certainly makes some attempt to remote the predicates, but for string equality, the processor can only do so if the remote server has a compatible collation. The query needs to evaluate = 'smith' according to the local collation, and the remote server may or may not be able to do so.

Steve Kass
Drew University
www.stevekass.com