How Can Server Links and OPENQUERY Facilitate Cross-Database Queries?

Cross database query: using server links and OPENQUERY

There are many ways to access data from different databases, one of which is to use server links and OPENQUERY.

Server link

To connect databases on different servers, you can use stored procedures sp_addlinkedserver to create server links. This allows you to access tables on the linked server and execute queries. To join tables from different databases, just prepend the table name with the name of the linked server. For example:

<code class="language-sql">SELECT *
FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]</code>

OPENQUERY

Alternatively, you can use OPENQUERY to remotely execute SQL statements and retrieve data without having to permanently connect to the server. The syntax is as follows:

<code class="language-sql">SELECT *
FROM OPENQUERY([LinkedServerName], 'SELECT * FROM [DatabaseName].[SchemaName].[TableName]')</code>

OPENQUERY improves performance by allowing remote servers to optimize query execution. Additionally, it can cache the results as a temporary table for subsequent queries.

Example

To illustrate the use of server links and OPENQUERY, consider the scenario of joining two tables in different databases on different servers.

Server link method:

<code class="language-sql">SELECT *
FROM [Database1].[dbo].[Table1] t1
INNER JOIN [LinkedServerName].[Database2].[dbo].[Table2] t2
ON t1.ID = t2.ID</code>

OPENQUERY method:

<code class="language-sql">-- 从远程数据库获取数据
SELECT *
INTO #TempTable
FROM OPENQUERY([LinkedServerName], 'SELECT * FROM [Database2].[dbo].[Table2]')

-- 将临时表与本地表连接
SELECT *
FROM [Database1].[dbo].[Table1] t1
INNER JOIN #TempTable t2 ON t1.ID = t2.ID</code>

The choice between these two methods depends on factors such as data size, query complexity, and performance requirements.

The above is the detailed content of How Can Server Links and OPENQUERY Facilitate Cross-Database Queries?. For more information, please follow other related articles on the PHP Chinese website!