Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to join a local table to a remote table by way of a linked server

By Stan Kulp,

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.

Total article views: 10434 | Views in the last 30 days: 11
 
Related Articles
FORUM

Display Query Execution Plan - SQL Server 2005

Display Query Execution Plan - SQL Server 2005

FORUM

Problem executing linked server query

Query failing to execute when run against linked server

FORUM

query execution order

query execution order

FORUM

Execute Query using DB2

Execute Query using DB2

FORUM

Query Execution Time

Query Execution Time

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones