Search code examples
sql-serverlinked-server

Does MSSQL always copy tables when using Linked Server

After googling and looking into the MS documenation (http://msdn.microsoft.com/en-us/library/ms188279.aspx) on linked servers I still couldn't get a clear answer to the following question. I'm thinking about linking 2 SQL Servers so I can create a subset of data from the source DB and insert it into an output DB (with duplicate checks before inserting) but I don't know how MSSQL processes queries that use linked databases.

As far as I know following query will result in LocalServer downloading the FarAwayTable and then executing the query locally (which is killing for performance in my case):

SELECT 
    f.* 
FROM 
    FarAwayServer.FarAwayDB.dbo.FarAwayTable f, 
    LocalServer.LocalDb.dbo.LocalTable l
WHERE
    f.ID = l.ID

My question is, will MSSQL do the same for the following query or will it only download the result (executing the whole query on the FarAwayServer):

SELECT 
    * 
FROM 
    FarAwayServer.FarAwayDB.dbo.FarAwayTable f
WHERE
    f.ID = 1
Solution

  • It will still act the same (the FarAwayTable table will be downloaded and the query will be executed locally). If you want to execute the query on FarAwayServer you should use OPENQUERY:

    SELECT * FROM OPENQUERY([FarAwayServer], 'SELECT * FROM FarAwayDB.dbo.FarAwayTable f WHERE f.ID = 1')