It'll cost you just a nickel.
Good morning SQL people! It’s time for another installment of SQL Server A to Z. We’re up to the letter O, which immediately brings to mind the song from Sesame Street, “Would you like to buy an O?” (Circular and sweet. “O” looks just like a donut. Really good enough to eat…)
You’re welcome.
OPENQUERY
Today, O stands for OPENQUERY. No matter how much you try to discourage it, somewhere, some time, your developers are going to want to use a linked server. So when that happens, it behooves you to have “the talk” with them about OPENQUERY. After all, if they’re going to fool around, they should at least use protection, right?
So what is OPENQUERY and what’s so great about it? OPENQUERY is a function provided in SQL Server that executes a pass-through query on a remote server. For example, if you needed to query a remote database via a linked server called MYSERVER you could do it the old-fashioned way:
SELECT customer_id, customer_name FROM MYSERVER.MyDatabase.dbo.Customers
Or you could use OPENQUERY:
SELECT * FROM OPENQUERY(MYSERVER, 'SELECT customer_id, customer_name FROM MyDatabase.dbo.Customers')
What’s the difference? Glad you asked. The difference is where the query is processed. With the traditional 4-part naming convention (link.database.schema.object) SQL Server brings all the data back to the local server and then processes it. When you use OPENQUERY, the data is processed on the remote server and then only the result set is returned. For the examples above, that really doesn’t make any difference. But what about the queries below?
SELECT customer_id, customer_name FROM MYSERVER.MyDatabase.dbo.Customers WHERE customer_id = 42
SELECT * FROM OPENQUERY(MYSERVER, 'SELECT customer_id, customer_name FROM MyDatabase.dbo.Customers WHERE customer_id = 42')
In the first query, all of the customer records are brought back to the local server and then they’re filtered for customer 42. I don’t know about you, but that doesn’t sound very efficient to me. The OPENQUERY statement, however, sends the entire query to the remote server for processing, so the remote server only returns the record for customer 42.
Hetergeneous queries
As if this weren’t reason enough to use OPENQUERY, it also helps avoid potential issues when querying non-SQL Server databases. Oracle, for example, contains some data types that are not compatible with some versions of SQL Server. Things like NUMBER defined without any scale or precision. In SSMS 2005, if you were to try to query a column like that through the four-part naming, you would get an error.
SELECT * FROM MYORACLE..STORE.CUSTOMERS
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “MYORACLE” supplied inconsistent metadata for a column. The column “CUSTOMER_ID” (compile-time ordinal 2) of object “”STORE”.”CUSTOMERS”” was reported to have a “DBTYPE” of 130 at compile time and 5 at run time.
And it wouldn’t matter if you weren’t even including that column in your query, you’d get the error anyway. By using openquery, however, you avoid the error. It also allows you to make use of any built-in functions of the remote RDBMS.
Limitations
One restriction to the use of OPENQUERY is that it does not accept variables for either parameter. This makes dynamic sql difficult, though not impossible. There’s also an 8000 character limit to the query you’re passing. And though you can pass through a query that returns multiple result sets, only the first result set will actually be returned.
And for no extra money, you get another sound for like "openquery"...