A linked server configuration enables SQL Server to execute distributed queries against tables on remote servers. Unfortunately, linked servers can also cause significant performance degradation because of the volume of network traffic generated between the servers and the fact that processing cycles are being consumed on both servers. If you must use a distributed query, a join with a remote table is best accomplished by inserting selected remote data into a local temporary table and joining the local table with the temporary table. A direct join is easiest to illustrate, however, and is just plain cool.

The first step in executing distributed queries is to add a linked server to the host server. There are two ways to accomplish this. One is through SQL Server Management Studio, and the other is through stored procedures.

The screen shot below shows connections to two servers in the Object Explorer panel of Management Studio, SERVER01 and SERVER02.

To link from SERVER01 to SERVER02, open the highlighted "Server Objects" node of SERVER01, right-click on "Linked Servers" and select "New Linked Server" from the menu.

Choose the "SQL Server" radio button of the "General" configuration page, enter "SERVER02" in the "Linked server" field, then click on the "Security" page link in the upper left panel.

Choose the "Be made using this security context" radio button from the "Security" configuration page, enter the appropriate username and password for the desired SERVER02 database into the "Remote login" and "With password" text boxes, then click the "OK" button.

There is now a "SERVER02" server object (highlighted in blue) under the "Linked Servers" node of SERVER01, confirming that the linked server has been added.

You can also confirm the addition of the linked server with the sp_linkedservers stored procedure...

...or by querying the sys.servers system table.

Instead of using SQL Server Management Studio's GUI, the linked server we just added could have been added with the following two stored procedure commands executed from a Management Studio query editor window connected to SERVER01.

EXEC sp_addlinkedserver 'SERVER02'
EXEC sp_addlinkedsrvlogin 'SERVER02',@useself = 'FALSE',@rmtuser = 'username',@rmtpassword = 'password'

Now that we have the host server (SERVER01) linked to the remote server (SERVER02), we are ready to execute a query joining tables on SERVER01 with a table on SERVER02.

Executing a query against linked servers requires the use of fully-qualified, four-part table names for each table in the query. This four-part name should be in the form:

[SERVER_NAME].[DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME]

Other than the fully-qualified four-part name requirement, writing a join on tables on linked servers is exactly the same as writing a join on tables on the same server.

We have installed the AdventureWorks Database on both SERVER01 and SERVER02. We are now going to join to one of the tables on our linked server (SERVER02) to tables on the host server (SERVER01) in the following query. This query will be executed from a Management Server query window connected to the host server.

SELECT a.FirstName,a.LastName,b.AddressLine1,b.City,c.StateProvinceCode
FROM [SERVER01].[AdventureWorks].[Person].[Contact] a
INNER JOIN [SERVER01].[AdventureWorks].[Person].[Address] b ON a.ContactID = b.AddressID
INNER JOIN [SERVER02].[AdventureWorks].[Person].[StateProvince] c ON b.StateProvinceID = c.StateProvinceID

The result of executing this query is the same as if all the tables were on the same server, except that the query consumes more server and network resources.

To drop the linked server using Management Studio, right-click on it and select "Delete" from the menu...

...then click the "OK" button on the "Delete Object" page.

Alternatively, you can execute the following stored procedure command to delete the linked server.

EXEC sp_dropserver 'SERVER02','droplogins'

To minimize the network overhead of joining directly with the remote table, it is better to insert the desired data from the remote table into a local temporary table and execute the join against the temporary table.

--EXECUTE THIS SCRIPT FROM A QUERY WINDOW CONNECTED TO SERVER01
--HOUSEKEEPING
BEGIN TRY
EXEC sp_dropserver 'SERVER02','droplogins'
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE #temp
END TRY
BEGIN CATCH
END CATCH
GO
--ADD THE LINKED SERVER
EXEC sp_addlinkedserver 'SERVER02'
GO
EXEC sp_addlinkedsrvlogin 'SERVER02',@useself = 'FALSE',@rmtuser = 'username',@rmtpassword = 'password'
GO
--POPULATE A TEMPORARY TABLE WITH DATA FROM THE LINKED SERVER
SELECT StateProvinceID,StateProvinceCode 
INTO #temp 
FROM [SERVER02].[AdventureWorks].[Person].[StateProvince]
GO
--JOIN HOST SERVER TABLES WITH THE TEMPORARY TABLE
SELECT a.FirstName,a.LastName,b.AddressLine1,b.City,c.StateProvinceCode
FROM [SERVER01].[AdventureWorks].[Person].[Contact] a
INNER JOIN [SERVER01].[AdventureWorks].[Person].[Address] b ON a.ContactID = b.AddressID
INNER JOIN #temp c ON b.StateProvinceID = c.StateProvinceID
GO
--DROP THE LINKED SERVER AND TEMPORARY TABLE
BEGIN TRY
EXEC sp_dropserver 'SERVER02','droplogins'
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE #temp
END TRY
BEGIN CATCH
END CATCH
GO

Caveat

Because of the increased overhead involved in executing distributed queries on linked servers, they should be avoided whenever possible.

An alternative to a distributed query would be to write an SSIS package that populates and synchronizes a local version of the remote table.