How to use SQL Database Query Editor in Azure Portal

By:   |   Updated: 2018-09-24   |   Comments   |   Related: More > Azure

Problem

Microsoft Azure offers tons of features and services. In some cases we need to execute queries in the Azure portal without SQL Server Management Studio. How can we do this?

Solution

Microsoft announced a new feature for Azure SQL database that is SQL Database Query Editor. This feature allows us to access Azure databases and execute queries in a browser. This tool is very handy for simple database operations in the Azure portal. The most important advantage of the query editor is that we can execute queries without the need to leave the Azure portal. Say you get a call from a client and they tell you about some problems and SQL Server Management Studio is not installed on the nearest computer where you can solve the problem. At this point, SQL Database Query Editor can be a life-saver. The fact is that, SQL Database Query Editor cannot take place of SQL Server Management Studio and it is not a competitor to SSMS. Query editor is more useful for simple CRUD (Create, Read, Update, Delete) operations.

How to connect using the Query Editor in Azure Portal

In this section, we will look at the connection settings of the query editor in the Azure portal.  Query editor is placed under the database main tab as shown below.

azure query editor

In the connection setting screen, the query editor allows us three types of authentication:

  • SQL Server authentication
  • Active Directory password authentication
  • Active Directory single sign on
connect to azure query editor

In this demonstration, we will use SQL Server authentication. We enter the login name and password and then click OK. After a successful login, the query editor screen will appear.

connect to azure query editor

The query editor screen has a very basic design.  On the left side, you can find an object explorer for tables, views and stored procedures. On the top of the screen you can find buttons for settings.

azure query editor explorer

Login: With the login button, you can change your connection type and credentials (user name, password).

azure query editor login

Edit Data: This option lets us change table records directly without the need to execute a query.  Click any table in the object explorer which you want to edit and then click Edit Data.  In this screen we can edit table records. It allows us to add, delete and insert new records.

azure query editor edit data

New Query: When we click this option, it opens a new query tab in the query editor screen.

azure query editor new query

Open query: With this option, we can access and open SQL query files which were saved before.

azure query editor open query

Save query: With this option, we can save queries to a local computer and reuse again. It saves these queries with a sql file extension.

Create table in query editor

We will create a table in Azure database with the query editor. If we want to execute a query we have to click the Run button.

CREATE TABLE EditorSampleTable
(ID int PRIMARY KEY IDENTITY(1,1),
UserName VARCHAR(100),
PostCode VARCHAR(200)
)
azure query editor run query

If you have a habit of using F5 to execute queries, you have to be careful because if you press F5 accidently in the query editor it will refresh the page and you will get the login screen and you will lose your query in the query editor.

CRUD Operations in Query Editor

In this section, we will demonstrate CRUD operations using the AdventureworksLT sample database for Azure. The following query will retrieve rows to a Results pane. At the end of the result panel you can see the query status and execution time.

SELECT * FROM SalesLT.Customer
azure query editor run query

The following query will INSERT rows into a table.

INSERT INTO EditorSampleTable
VALUES('Name1','3400'),('Name2','3500'),('Name3','3400')
azure query editor run query

Things to know when using SQL Azure Query Editor

In the following part of this tip we will discuss about some considerations and limitations of the SQL Azure Query Editor.

If you don’t enable Allow Access to Azure services, you cannot connect to the query editor. If the Allow Access to Azure services option is off, you will get this error when you login with the query editor.

azure query editor login issues

This option is placed in the Azure SQL databases firewall settings and you can enable or disable this option.

azure query editor configuration settings

Or you can enable the option with following system stored procedure in the master database. Also, the query editor does not allow you to connect the master database. For this reason, you have to execute this procedure in SSMS.

IF NOT EXISTS 
(SELECT * FROM sys.firewall_rules WHERE name='AllowAllWindowsAzureIps')
BEGIN
Exec sp_set_firewall_rule N'AllowAllWindowsAzureIps','0.0.0.0','0.0.0.0'
PRINT 'Allow Access to Azure services enabled'
END
ELSE
BEGIN
PRINT ' Azure services enabled before'
END

The query editor does not return more than one result set. So, let's execute the below query and review the result set.

SELECT top 1 * FROM SalesLT.Customer
GO
SELECT top 1 * FROM SalesLT.Product

The result set will look like this. The Query Editor only shows the result of the last executed query.

azure query editor run query

Usage Experience

In this section I want to mention my personal use and test experiences. When I opened the TIME and I/O statistics and tried to execute the below query, the query editor does not display the statistics details.

SET STATISTICS TIME ON;  
SET STATISTICS IO ON
GO
SELECT top 1 * FROM SalesLT.Customer
azure query editor run query

My second test was to use a transaction block. I tried to execute an explicit transaction. In the first step I started a transaction without any commit and rollback statement.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE EditorSampleTable
SET UserName='XXXXX'
WWHERE ID=1
azure query editor run query

Everything seems to be fine. Then I tried to commit open transaction and I got an error as shown below.

azure query editor run query

At first this error did not make sense and I did an internet search to find some details about this error and could not find any details. Finally I, posted a question and Grant Fritchey answered it and we decided the error is a sort of bug.

Summary

In this tip we explored the SQL Database Query Editor pros and cons. It is a very handy tool for some basic operations, but it still has some limitations and unsupported features. Also, it is still in preview (Services running in preview mode are made available for testing, evaluation, proof-of-concepts and reporting issues and feedback to Microsoft) version. In my mind, it is a bit early to use SQL Database Query Editor for more complex operations, but it can be useful for specific operations.

Next Steps


Last Updated: 2018-09-24


get scripts

next tip button



About the author
MSSQLTips author Esat Erkec Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools