How to Efficiently Join Tables Across Different Database Servers?

Join tables across different server databases

When retrieving data from multiple tables in different databases located on different servers, there are several ways to join these tables efficiently.

1. Use server link

Create server links via the sp_addlinkedserver command to establish connections between databases. Access via query as usual, prefixing the linked server name with the database name, as shown in the example:

<code class="language-sql">-- 来自 DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
ON tab1.ID = tab2.ID</code>

2. Use OPENQUERY

Alternatively, consider using OPENQUERY to execute remote SQL statements and retrieve data from other servers. This method optimizes bandwidth and optimizes queries on the remote server. You can cache data into a temporary or in-memory table for later connection, as shown in the following example:

<code class="language-sql">-- 从其他数据库服务器获取数据
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

-- 现在我可以连接我的临时表以查看数据
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID</code>

Notes

The choice between

Server Link and OPENQUERY depends on the specific use case and performance requirements. Server links provide a permanent connection, while OPENQUERY allows temporary data retrieval. For scenarios that require filtering, OPENQUERY may be more efficient. Please refer to the documentation for more examples and scenarios.

The above is the detailed content of How to Efficiently Join Tables Across Different Database Servers?. For more information, please follow other related articles on the PHP Chinese website!