Why is using OPENQUERY on a local server bad?

问题:

I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement like this:

exec('INSERT INTO tabl SELECT * FROM OPENQUERY(@Server, @AdHocView)')

However, I've heard that using OPENQUERY on the local server is frowned upon. Could someone elaborate as to why?

回答1:

• Although the query may return multiple result sets, OPENQUERY returns only the first one.
• OPENQUERY does not accept variables for its arguments.
• OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name.
• If the sp_addlinkedserver stored procedure is used within same script, the credentials used on the remote server are hardcoded into the script, visible to anyone who has a copy

Reference:

• OPENQUERY

回答2:

In addition to what @OMG Ponies said, it's simply unnecessary. There's no reason to introduce ad-hoc query and distributed transaction semantics when you don't have to. When you use OPENQUERY you take on all of the negative aspects of dynamic SQL, including less predictable plans and the server's inability to accurately track dependencies.

OPENQUERY also requires the local user to have permissions to the target server, which is probably not what you want unless it's an administrative script. You can't expect every user of one database to have the same permissions to every other database.

回答3:

Just a followup.

OpenQuery is good when you have to compare or manipulate some rowsets from stored procedures.

for example if you have to compare results from two servers (test and rollout server) when you migrate from SQL Server 2005 to SQL server 2008 for example, then you can do the following query:

select * into test_table from OpenQuery(testServer, 'exec testdb.dbo.test_sp');
select * into rollout_table from OpenQuery(rolloutServer, 'exec testdb.dbo.test_sp');

select * from test_table
except
select * from rollout_table;

select * from rollout_table
except
select * from test_table;

to see any discrepancies.