通过

比较查询远程执行选项

在 SQL Server 中，有三种远程执行查询的方法：

  • OPENQUERY
  • OPENROWSET
  • EXECUTE AT

本文介绍了这三种方法。

OPENQUERY

OPENQUERY (Transact-SQL)

在指定的链接服务器上执行指定的传递查询。 该服务器是 OLE DB 数据源。 在查询中，在 FROM 中使用 OPENQUERY，就好像它是表名一样。 还可以将 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。 依据 OLE DB 提供程序的功能，还可以将 OPENROWSET 用作 INSERT、UPDATE 或 DELETE 语句的目标表。 尽管查询可能返回多个结果集，但 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。

如果性能很重要，请测试远程查询：

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

示例

A. 使用 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...) 的其他示例，请参阅以下主题：

另请参阅