MS SQL Server provides several ways to execute remote (distributed) queries, but choosing the wrong approach can easily lead to performance bottlenecks, security exposure, and long-term maintenance issues.
Remote queries allow you to access tables on different servers or instances, perform data filtering and transformation across servers.
In this article, I will compare the four most common approaches,
For each method, I will cover,
How it works, where is it useful, what are the cons and a simple example.
At the end, I will explain why OPENQUERY is usually the best choice in real-world production systems, when combined with proper Linked Server configuration.
Before each test, enable statistics with command below.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
These settings will help us compare Logical reads, CPU time, execution behavior.
--
Linked Server
This is the most common way to query remote data in SQL Server.
Easy to write and read. No need for dynamic SQL for execution and variables.
Linked Server passwords are not hashed, but they are stored encrypted and never exposed in clear text, it provides a secure authentication.
Has mapping options (impersonate current login, use a specific remote login, or a fallback).
Using Linked Servers ensures that there is a clear, controlled configuration of the remote connection with proper authentication, encryption, and validation.
Filters are not always pushed to the remote server and this can result in large data transfers, high network usage, poor performance on large tables. In many cases, SQL Server will pull all rows from the remote table and apply the WHERE filter locally.
Connection is reusable.
sp_addlinkedserver 'w19';
SELECT * FROM [w19].[AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > '2011-05-31';
CPU Time: 4717 ms
Elapsed Time: 7734 ms
--
OPENQUERY
Query is executed entirely on the remote server. You must use Dynamic SQL. Filters and joins are applied remotely. It is a best choice for large tables and production workloads. OPENQUERY inherits Linked Server security and configuration. When querying non-SQL Server databases (ORACLE, MySql, PostgreSQL) this is the way. Connection is reusable.
SELECT * FROM OPENQUERY([w19], '
SELECT * FROM [AdventureWorks2022].[Sales].[SalesOrderDetail]
WHERE ModifiedDate > ''2011-05-31''');
CPU Time: 3367 ms
Elapsed Time: 5443 ms
OPENQUERY is designed to work with linked servers, meaning it sends a query to another server, which could be a remote database or a file system via a linked provider (like OLE DB or an ODBC driver) but you can do for example CSV also. For large files OPENROWSET tends to be faster.
You would need first to create path with Linked Server, and then you can use OPENQUERY. Code below:
EXEC sp_addlinkedserver
@server = 'FileServer',
@srvproduct = '',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Temp\EmployeeData.csv',
@provstr = 'Text;HDR=YES;FMT=CSVDelimited';
SELECT *
FROM OPENQUERY(FileServer, 'SELECT * FROM [EmployeeData.csv]')
One of the major advantages of using Linked Servers with OPENQUERY is the ability to centrally control and redirect connections. In a single script, you can point your links to different servers, for example switching between DEV, UAT, or production environments, without changing your query code.
SELECT name, provider, data_source FROM sys.servers;
DECLARE @name sysname = 'w19', @datasource sysname = 'w19\sql';
EXECUTE sp_setnetname @server = @name, @netname = @datasource;
The next two options - OPENROWSET and OPENDATASOURCE require AdHoc Distributed Queries to be enabled on the instance.
They are disabled for security reasons. When this feature is enabled, it allows users to execute queries that can directly access remote data sources without needing to configure a Linked Server.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
--
OPENROWSET
It is mainly designed for ad-hoc access to external data sources. Harder to govern. Unlike Linked Server and OPENQUERY, it does not reuse connections. Each execution performs a new connection handshake, authentication, query execution, and disconnect. This makes it unsuitable for frequent or high-volume workloads. However it is good for reading external files (CSV, Excel), temporary data access without creating a Linked Server (connection is direct). It allows 3-part-name in connection.
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended. Requires DSN or inline credentials (Password Exposure).
SELECT * FROM OPENROWSET(
'SQLNCLI',
'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;',
'SELECT * FROM Sales.SalesOrderDetail
WHERE ModifiedDate>= ''2011-05-31''''
);
-- CSV/Excel example:
SELECT * FROM OPENROWSET(
BULK 'C:\\Temp\\EmployeeData.csv',
FORMAT = 'CSV',
FIRSTROW = 2
) AS data;
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=NO;
Database=C:\Temp\EmployeeData.xlsx',
[Sheet1$]);
CPU Time: 4503 ms
Elapsed Time: 7594 ms
--
OPENDATASOURCE
This is very similar to OPENROWSET and shares most of the same limitations. It also performs a new connection handshake on each execution.
You do not send a query string, you expose the remote server as a temporary data source and reference objects (4-part-name in connection)..
DML INSERT, UPDATE, DELETE commands are unreliable and not recommended.
Requires DSN or inline credentials (Password Exposure).
SELECT * FROM OPENDATASOURCE(
'SQLNCLI',
'Server=w19;Database=AdventureWorks2022;Trusted_Connection=yes;'
).AdventureWorks2022.Sales.SalesOrderDetail;
-- CSV example:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Text;Database=C:\Temp;HDR=YES')..[EmployeeData#csv]
CPU Time: 4592 ms
Elapsed Time: 7718 ms
I was using AdventureWorks sample database for these examples.
Top comments (0)