SQL – Openquery (1) Use It Or Not Use It

From Transact-SQL book: “Openquery executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.”

This is a typical Openquery that points to a DB2 linked server used in the From clause.

Although OPENQUERY is a convenient way to dynamically query data via a linked server, it has many limitations. Here are what I can think of:

1) Performance: I’ve observed many OPENQUERY failed with unexplained “Interruption” from the remote server in production environment when the SQL and/or the linked server are overloaded.

2) There is an allowable number of simultaneous OPENQUERY connections, 50, 40… (not sure). Rest of the OPENQUERY connections will fail when the server reaches the limit.

3) Cannot call stored procedures on the linked server.

4) Although we can use INSERT, UPDATE, or DELETE statement with OPENQUERY, it is subject to the capabilities of the OLE DB provider, and the syntax is not straightforward (will talk about this later).

5) When temp tables used inside OPENQUERY, the performance of the query deteriorate dramatically. To resolve the performance issue, very often we have to abandon OPENQUERY, and move the entire process to a SSIS package to take advantage of staging tables etc.

6) Anybody who is crazy enough to have more than 8000 chars inside OPENQUERY will tell you that this is not possible. SQL server will tell you plainly that the query cannot have more than 8000 chars.