What is the reasoning for using OPENQUERY within a tsql stored procedure?

Question

I am currently reviewing some jobs that run stored procedures on a database. All of these stored procedures are connecting to a linked server(s). I am not too familiar with this functionality. I am at the moment attempting to determine why these were used versus just a normal query as the queries I am running seem to be pulling in the data.

I read this, which is MSDNs explanation of openquery. :

http://technet.microsoft.com/en-us/library/ms188427.aspx

I also read this, which is a stackoverflow link talking about why not to use it on local server. :

Why is using OPENQUERY on a local server bad?

My question is do you basically just use this when the stored procedure requires the embedded credentials of the linked server? Or are there more reasons for using OpenQuery that I am not aware of?

Answer 1

Two advantages I can think of using openquery. It can reduce the amount of data you'd need to transfer by doing the necessary filtering on the remote server. It can allow the query optimizer on the remote server to choose the optimal execution plan when joining tables.

The other alternative is using REMOTE JOIN I've had some luck using it but Aaron Bertrand has a nice write up about it here.. http://www.mssqltips.com/sqlservertip/2765/revisit-your-use-of-the-sql-server-remote-join-hint/

Here is the MS documentation

REMOTE

Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table. If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table. If both tables are remote tables from the same data source, REMOTE is not required. REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause. REMOTE can be used only for INNER JOIN operations.