Inconsistent results between remote and local query

Question

I have an interesting case of results sets returned by SQL Server being different depending on whether a query is executed locally or remotely.

In essence, if I execute the following query:

SELECT p.ID AS Id 
FROM csdb.users.People AS p
LEFT JOIN csdb.users.Accounts AS a ON p.ID = a.IdentityId

then the left join is performed, and I obtain 633 rows back. However, if I execute the query remotely as:

SELECT p.ID AS Id 
FROM quantdb.csdb.users.People AS p
LEFT JOIN quantdb.csdb.users.Accounts AS a ON p.ID = a.IdentityId

then the left join is not performed, an index scan of the People table is performed, and only 564 records are returned. In essence it seems as if in the remote case the optimiser is saying 'well, you are not requiring any of the columns from the Accounts table so I won't do the join' whereas in the other case it is saying 'I will do the join'.

Is this difference in behaviour of the optimiser for remote and local queries a known 'feature' of SQL Server, or is this a bug?

We are using SQL Server 2008, SP1

Answer 1

Not doing the join is a valid optimization if there is a unique constraint on Accounts.IdentityId, because the join cannot add any new row (a non-unique column would cause p.ID to repeat for each corresponding duplicate in Accounts.IdentityId).

Can you post the exact schema of the tables, including all indexes and constraints? I want to see if it reproes.