Using OPENQUERY in SQL Server

In this article, we will learn about the OPENQUERY function in SQL Server and its usage.

What is OPENQUERY

The OPENQUERY is a SQL Server function and it is used to execute a query on a linked server. We can connect and retrieve data from remote data sources and databases using OPENQUERY function.

The syntax of OPENQUERY() is following:

SELECT * FROM OPENQUERY(linked_server_hostname, 'query')

1. linked_server_hostname: specify the name of the linked server on which you want to execute the query.
2. query: Specify the SQL query that you want to execute on the linked server.

Now, let us deep down in the OPENQUERY function. In the article, I am discussing the following topics which help everyone to understand how to configure and use it.

First, let us review the pre-requisite of OPENQUERY.

Pre-requisites for Using OPENQUERY

There are a few prerequisites for using the OPENQUERY function. Here is the list.

• The OPENQUERY function is used to run CRUD operations on a linked server therefore a linked server must be configured on the server on which you are executing a pass-through query. You can read the article to learn more about SQL Server linked servers and how to configure them.
• The account that is using OPENQUERY to access remote data sources must have read and execute permissions on the remote server.
• The OPENQUERY is used to query the remote servers so the firewall must be configured properly so the query can access the remote server.
• DTC (Microsoft distributed transaction coordinator) must be running.

Setting Up OPENQUERY

To use OPENQUERY, we must create a linked server. In this article, we will create a linked server between SQL Server and MySQL. I have restored a database named sakila on MySQL Server which I have installed on my computer. We are going to use the same database to understand the usage of linked server.

In this article, I am not explaining the step-by-step process of creating a linked server rather I am sharing the code that creates a linked server between SQL Server and MySQL. You can refer Create linked servers article to learn more about creating linked server using SQL Server management studio.

The code to create a linked server is following:

USE [master]
go
EXEC master.dbo.Sp_addlinkedserver
@server = N'MYSQLSERVER',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@datasrc=N'SAKILA',
@catalog=N'sakila'

EXEC master.dbo.Sp_addlinkedsrvlogin
@rmtsrvname=N'MYSQLSERVER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword='########'

go

Once a linked server is created, you can view the details by executing the following query:

use master
go

select
server_id [Server ID], name [Linked Server Name],
product [Database Product], data_source [Data Source Name],
catalog [Database Name] , modify_date [Modified Date]
from sys.servers

Query output

As you can see, the linked server named MYSQLSERVER has been created successfully and we are ready to use OPENQUERY to get data from MySQL Server.

Working with OPENQUERY

In this section, we will learn how to perform DDL operations using OPENQUERY. To demonstrate, I have installed the world database in MySQL Server. The database has a table named country which keeps details of all countries of the world.

Whenever we perform INSERT, UPDATE, and DELETE statements, we must construct the statements.

First, we will learn how to insert data in a table using INSERT Statement. The syntax of the INSERT statement is following:

INSERT OPENQUERY (<linked_server_name>, 'select query') values (<val_1>,<val_2>..)

As you can observe, the syntax of INSERT statement is very different than the traditional INSERT statement.

In the syntax

• Linked_server_name: Specify the linked server name.
• Select_query: Specify the select query of the table in which you want to add the data.

For example, I want to insert details of India in the country table. To do that, the INSERT query will be as follows:

INSERT OPENQUERY ([MYSQLSERVER], 'SELECT Code,
Name,
Continent,
Region,
SurfaceArea,
IndepYear,
Population,
LifeExpectancy,
GNP,
GNPOld,
LocalName,
GovernmentForm,
HeadOfState,
Capital,
Code2 from world.country')
VALUES ('IND' ,
'India',
'Asia',
'Southern and Central Asia',
3287263.00,
'1947' ,
1013662000,
62.5 ,
447114 ,
430572 ,
'Bharat/India' ,
'Federal Republic' ,
'Kocheril Raman Narayanan',
1109 ,
'IN');

Run below query to verify the data

select * from OPENQUERY ([MYSQLSERVER], 'select * from world.country where code=''IND''')

Query output

As you can see, the data has been inserted.

Next, we will learn to use OPENQUERY in the UPDATE statement to change a record in a remote database.

The syntax of UPDATE statement is following:

UPDATE OPENQUERY (<linked_server_name>, select_query_with_condition') values col_1=<new_value>

In the syntax

• Linked_server_name: Specify the linked server name.
• Select_query_with_condition: Specify the SELECT statement with the appropriate WHERE condition to get the record that you want to change.
• Col_1: Specify the name of the column whose value you want to update.
• New_value: specify new value.

Let us understand it using an example. Suppose for a country named India, we want to change the LocalName value from ‘Bharat/India’ to ‘Bharat/Hindustan’.

The update query should be written as follows:

UPDATE OPENQUERY([MYSQLSERVER], 'select localname from world.country where Code=''IND'';')
SET localname = 'Bharat/Hindustan'

Execute below query to verify the data:

select * from OPENQUERY ([MYSQLSERVER], 'select * from world.country where Code=''IND'';')

Query output

As you can see, the localname has been changed.

Now, let us learn how to delete a record from a remote database using OPENQUERY.

Next, we will learn to use OPENQUERY in the DELETE statement to remove a record in a remote database.

The syntax of DELETE statement is following:

DELETE OPENQUERY (<linked_server_name>, select_query_with_condition')

In the syntax

• Linked_server_name: Specify the linked server name.
• Select_query_with_condition: Specify the SELECT statement with the appropriate WHERE condition to get the desired record that you want to remove.

Let us understand it using an example. Suppose we want to remove the details of country India. The query should be written as follows:

DELETE OPENQUERY([MYSQLSERVER], 'select * from world.country where Code=''IND'';')

Once the query executes, run the below query to verify the data.

select * from OPENQUERY ([MYSQLSERVER], 'select * from world.country where Code=''IND'';')

Query output

Advanced Features of OPENQUERY

Here are some advanced features of the OPENQUERY feature:

• We can use the parameters in the OPENQUERY to make the query dynamic and flexible.
• We can perform joins between local and remote tables using OPENQUERY.
• We can execute the stored procedures on a remote server using the OPENQUERY.
• We can perform the distributed transactions using OPENQUERY.

Comparing OPENQUERY with Other Query Methods

Along with OPENQUERY, we can also use the linked server without specifying the OPENQUERY function to query the remote data source.

Linked Server without OPENQUERY:

We can use linked server without using OPENQUERY. We can use four-part naming to access data from the remote data source. The syntax of doing it is following:

Select * from [RemoteDatasourceName].[databasename].[schemaname].[tablename]

In the syntax

• Remotedatasourcename: Specify the linked server name
• DatabaseName: Specify the remote database name
• SchemaName: Specify the name of the schema of the remote database.
• Tablename: Specify the table name.

Performance consideration:

The linked server fetches the record from remote data source and process it on local SQL Server so there are some factors which might hamper the performance:

• When we use a linked server, the SQL Server uses the least optimized query execution plan because it is not aware of the statistics of the remote table hence it is unable to create an optimal plan. For smaller tables, it does not impact a lot but for larger table, you might start seeing blockings.
• The amount of data to be transferred on wire and network latency is higher than user might face slow performance.
• The linked server never caches the data therefore even if the same query executes, the SQL Server will fetch data from the physical table.

New in dbForge Tools: Support for OPENQUERY

As we know, the SQL Server management studio has IntelliSense, which is a very powerful auto-complete tool but it has some limitations. The developers who are not familiar with the syntax of OPENQUERY might struggle to use them in the application coding. Certain tools fill this gap and also provide more flexibility in terms of customizing auto-complete and snippets. One of my favorite tools is SQL Complete which is a part of Devart SQL Tools.

I am glad to announce that dbForge SQL Tools 6.5, dbForge SQL Complete 6.15, and dbForge Studio for SQL Server 6.5 have added the support of OPENQUERY, OPENROWSET, and other important functions. Here is the snap of the SQL Editor of dbForge Studio for SQL Server.

Hope this functionality helps developer and DBAs to improve their coding efficiency and speed.

Conclusion

The OPENQUERY function is a very useful function for querying the remote data sources. Even though the syntax of OPENQUERY is complicated, if you consider the performance of the query, it is good to use the OPENQUERY function instead of executing queries directly on the linked server.

In this article, we learned about the OPENQUERY function of SQL Server and how it is used. We learned what are the prerequisites for using it. Moreover, we learned how to execute various DML statements on remote databases using the OPENQUERY function.