Difference between Inner Join VS Inner Remote Join

Question

I don't know where I have to ask this question, I just want to know what is the difference between Inner Join and Inner Remote Join ? I have just tried implemented both joins like below:

With Remote Inner Join

SELECT P.CompanyName,u.UserName from tb_Offices AS P
INNER REMOTE JOIN 
tb_Users AS U
on P.UserId=U.UserId

With Simple Inner Join

SELECT P.CompanyName,u.UserName from tb_Offices AS P
INNER JOIN 
tb_Users AS U
on P.UserId=U.UserId

Both Queries returns same kind of records.

Then I tried run with Execution plan that is :

With Remote Inner Join I got :

and with simple Inner Join I got :

I am not so much friendly with SQL Execution Plan.

I just wanted to know which is better between Inner Join and Inner Remote Join.

Thanks

Answer 1

If you look at the messages tab you will see

Warning: The join order has been enforced because a local join hint is used.

When you use the INNER REMOTE JOIN hint you are forcing the tables to be joined in the order as written rather than allowing it to explore all possible join permutations.

So a similar result to specifying OPTION (FORCE ORDER)

This explains the different execution plans.

This hint is not intended to be used for local tables.

An example where inadvertently forcing the join order this way would be extremely negative is below - as it forces the large tables A and B to be joined first before eliminating all rows with the join on C. When the optimiser is not constrained (first plan below) it reorders things to (C x A) x B and the plan is much more efficient.

CREATE TABLE #A(X INT PRIMARY KEY);
CREATE TABLE #B(X INT PRIMARY KEY);
CREATE TABLE #C(X INT PRIMARY KEY);

INSERT INTO #A 
SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY @@SPID) FROM master..spt_values v1, master..spt_values v2

INSERT INTO #B 
SELECT * FROM #A

SELECT *
FROM #A INNER JOIN #B ON #A.X = #B.X 
        INNER JOIN #C ON #A.X = #C.X 

SELECT *
FROM #A INNER REMOTE JOIN #B ON #A.X = #B.X 
        INNER JOIN #C ON #A.X = #C.X 

DROP TABLE #A, #B,#C

Answer 2

You should try to use REMOTE only if the right table is on the Remote server and left is local. Also REMOTE should be used only when the left table has fewer rows than the right table.These are documented at https://msdn.microsoft.com/en-us/library/ms173815.aspx