JOIN on table on another database server vs. JOIN on a SELECT on table on another server

Question

I’m curious if there is any difference in performance between JOINing on a table residing on another SQL server instance and JOINing on a subset of that same table the other server instance. In other words, would performance be the same for the following two queries:

SELECT t1.CustomerName, t2.Address, t2.Phone 
FROM Table1 t1
LEFT JOIN [Server X].dbo.Table2 t2 on t2.CustomerID = t1.CustomerID

And

SELECT t1.CustomerName, t2.Address, t2.Phone
FROM Table1 t1
LEFT JOIN (SELECT CustomerID, Address. Phone FROM [Server X].dbo.Table2) 
t2 
on t2.CustomerID = t1.CustomerID

We can assume that Table2 contains more than just these two columns. I’m wondering if SELECTing only the columns I need vs. JOINing on the entire table will make any sort of difference, especially given this is a cross server query.

Answer 1

Off the top of my head, I'm not sure, but without testing it, it looks to me like SQL would execute these the same. You can check this out if you have SQL Server Management Studio and run the execution plan.

I believe the top is more efficient (as it would be on the same server) if the inner select were more complex. It's really up to the optimizer.