Lowell (7/24/2014)
SELECT * From myTableINNER JOIN MyLinkedServer.SandBox.dbo.AnotherTable myAlias
ON MyTable.ID = MyAlias.ID
WHERE myAlias.CustomerID = 12
looks pretty simple, right? with a WHERE statement that specific, i'd expect very few rows, since how many rows have that specific customerID?
the problem is behind the scenes, the entire contents of the linked server table MyLinkedServer.SandBox.dbo.AnotherTable gets copied over into tempDB, and then joined to the local table, and then that gets filtered like a normal query.
I don't believe that's true. SQL does not always have to copy the rows over to the local server, and of course it won't if it can avoid it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.