Using OPENQUERY to Access Large Remote Objects
Accessing large remote objects requires very little extra effort beyond that discussed in the preceding examples, but there are performance drawbacks. Queries run locally and not on the remote servers, which means that the OLE layer may draw the needed row sets across the link to be processed. This can be a performance nightmare with large remote objects.
For linked servers, the OPENQUERY function is the answer. This function allows queries to pass through and execute on the linked server. The function is used in the FROM clause of a query statement, with the following syntax:
OPENQUERY(linkname, 'SQL statement')
where linkname is the name of the linked server and the SQL statement is included in single quotes ('). Neither argument of the function accepts variables. This somewhat limits the usefulness of the function, but there are ways to get around this limitation.
Let's assume that the product_id and product_name columns from the table products on the STAGING server are required. Using the OPENQUERY function, the SQL looks like this:
SELECT * FROM OPENQUERY(STAGING, 'SELECT product_id, product_name FROM products')
This statement returns the recordset from the STAGING linked server. Since the OPENQUERY function is used in the FROM clause, this gives OPENQUERY a lot of SQL flexibility. The function can have an alias and be joined to other tables. Multiple OPENQUERY statements can be run to access different linked servers, in order to join results. Inserts, updates, and deletes can also be passed through the OPENQUERY function.
Variables cannot be passed into the OPENQUERY function, so the SQL statement needs to be constructed as a string and then passed into the stored procedure sp_executesql as the SQL statement parameter. This is not the most convenient method, but can work efficiently. Calling remote stored procedures is another possibility and often a better solution.