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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".