比较查询远程执行选项

在 SQL Server 中，可通过三种方法远程执行查询：

  • OPENQUERY
  • OPENROWSET
  • EXECUTE AT

本文介绍这三种方法。

OPENQUERY

OPENQUERY （Transact-SQL）

在指定的链接服务器上执行指定的传递查询。 该服务器是 OLE DB 数据源。 在查询中将OPENQUERY作为表名称在FROM中使用。 可以将OPENQUERY作为INSERT、UPDATE或DELETE语句的目标表进行引用。 这取决于 OLE DB 提供程序的功能。 尽管查询可能返回多个结果集，但 OPENQUERY 只返回第一个结果集。

OPENQUERY 需要预先添加和配置的链接服务器以及向远程服务器发送请求文本。 OPENQUERY 不需要四部分名称约定才能访问对象。

OPENROWSET

OPENROWSET （Transact-SQL）

包括从 OLE DB 数据源访问远程数据所需的所有连接信息。 当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。 若要更频繁地引用 OLE DB 数据源，请考虑使用 链接服务器、 PolyBase 或通过 SQL Server Integration Services（SSIS） 或自定义应用程序等工具在两个数据源之间直接连接。

在查询中，在 FROM 子句中使用 OPENROWSET。 可以将OPENROWSET用作INSERT、UPDATE或DELETE语句的目标表，前提是OLE DB 提供程序的功能允许这样。 尽管查询可能返回多个结果集，但 OPENROWSET 只返回第一个结果集。

OPENROWSET 还支持通过内置 BULK 提供程序执行批量操作，使文件中的数据能够以行集的形式读取和返回。

有关其他信息，OPENROWSET 使用明确编写的连接字符串。

EXECUTE AT

EXECUTE （Transact-SQL）

允许动态 SQL 针对链接服务器运行。 调用的参数 EXECUTE 之一是 AT，旨在绕过 OPENQUERY 和 OPENROWSET 限制。 EXECUTE (``<query>``) AT [<linked server>] 是动态 SQL，可以从远程服务器返回任意数目的结果集。

动态 SQL 指南

避免在应用程序中使用动态 SQL 命令，并限制有权访问动态 SQL 命令的用户的权限。 通过 EXECUTE 执行的查询的构造可能会通过 SQL 注入攻击为网站和应用程序带来漏洞。 有关详细信息，请参阅 SQL Injection。

当性能成为关注点时，测试远程查询：

  • 确保在远程服务器上执行尽可能多的逻辑
  • 验证远程服务器索引表上的索引是否适当地支持查询
  • 请注意，在代码库中使用远程查询会使数据库代码版本控制和开发和测试环境的维护复杂化

示例

答： 使用 OPENQUERY 执行 SELECT 直通查询

以下示例使用传递 SELECT 查询来选择包含 OPENQUERY以下内容的行：

SELECT * 
    FROM OPENQUERY ([linkedserver], 
        'SELECT * FROM AdventureWorksLT.SalesLT.Customer');

B. 使用 OPENROWSET 执行 SELECT 透传查询

以下示例使用传递 SELECT 查询来选择符合条件的行 OPENROWSET。

SELECT a.*
FROM OPENROWSET('MSOLEDBSQL', [linkedserver],
     'SELECT * FROM AdventureWorksLT.SalesLT.Customer') AS a;

已从 SQL Server 2022 (16.x) 和 SQL Server Management Studio 19 (SSMS) 中移除 SQL Server Native Client（通常缩写为 SNAC）。 不建议在新的开发工作中使用 SQL Server Native Client OLE DB 提供程序（SQLNCLI 或 SQLNCLI11）和旧版 Microsoft OLE DB Provider for SQL Server (SQLOLEDB)。 此后请切换到新的 Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server。

C. 使用 EXECUTE AT 执行 SELECT 直通查询

以下示例使用传递 SELECT 查询来选择满足条件 EXECUTE ... AT 的行

EXECUTE ('SELECT * FROM AdventureWorksLT.SalesLT.Customer') AT [linkedserver]

D. 执行多个 SELECT 语句

以下示例使用传递 SELECT 查询并获取多个结果集

EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
    SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [linkedserver];

E. 执行 SELECT 并传递两个参数

以下示例使用具有两个参数的传递SELECT

EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [linkedserver];

F. 使用变量执行 SELECT 并传递两个参数

以下示例使用具有两个参数的传递 SELECT，方法是使用变量

DECLARE @CustomerID AS INT
DECLARE @LastName AS VARCHAR(100)
SET @CustomerID = 10
SET @LastName = 'Garza'
EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [linkedserver];

G. 使用链接服务器通过 EXECUTE 执行 DDL 语句

以下示例在链接服务器上使用 DDL 语句

EXECUTE (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1
(
    Column1 INT
)' ) AT [linkedserver];

完成测试后，清理已创建的对象

EXECUTE (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1'
) AT [linkedserver];
EXECUTE sp_dropserver 'linkedserver'

其他示例

有关显示使用 INSERT...SELECT * FROM OPENROWSET(BULK...)的其他示例，请参阅以下主题：

另请参阅

  • Last updated on