380
Part III: Advanced T-SQL Data Types and Querying Techniques
Extended Properties=Excel 5.0'
)...People
SET FirstName = 'Catherine'Pass-Through Distributed Queries
A pass-through query executes at the external data source and returns the results to the
calling SQL Server. The primary reason to use a pass-through query is to reduce the amount
of data passed from the server and the client. Instead of pulling 1 million rows into SQL
Server so that it can use only a fraction of them, it may be easier to select just the rows it
needs from the external data source.A pass-through query must use the query syntax of the external source. For example, if the
external data source is Oracle, you must use the PL/SQL in the pass-through query.In the case of a pass-through query that modifi es data, the remote data type determines
whether you locally or remotely perform the update:■ (^) When another SQL Server is updated, the remote SQL Server performs the update.
■ (^) When non-SQL Server data is updated, the data providers determine where to per-
form the update.
Two forms of local distributed queries exist: one for linked servers and one for external
data sources defi ned in the query. Likewise, two forms of explicitly declaring pass-through
distributed queries exist as well; OpenQuery() uses an established linked server, and
OpenRowSet() declares the link within the query.
The key to using pass-through queries, when using SQL Server as the external data source,
is understanding how they execute and how to construct the query for optimal perfor-
mance. Depending on the FROM and WHERE clauses, SQL Server attempts to pass as much of
the query as possible to the external SQL Server to improve performance.
OpenQuery()
The OpenQuery() function uses a linked server, so it is certainly the easiest to develop
and use. You can also use it within DML statements as a table, requiring only two param-
eters: the name of the linked server and the pass-through query.
The following illustrates a simple example using the OpenQuery() function to query an
external SQL Server instance.
SELECT
FROM OPENQUERY(AvalonDev, 'SELECT FROM Person.Contact WHERE
ContactID = 1')
The OpenQuery() function requires a low amount of processing by SQL Server. In the pre-
vious example, exactly one row was returned to SQL Server. The engine executes the WHERE
clause as it reads from the database.
c15.indd 380c15.indd 380 7/30/2012 4:50:40 PM7/30/2012 4:50:40 PM
http://www.it-ebooks.info