How to use SQL Database Query Editor in Azure Portal (2024)

By: Esat Erkec |Comments | Related: > Azure

Problem

Microsoft Azure offers tons of features and services. In some cases we need toexecute queries in the Azure portal withoutSQL Server Management Studio. How canwe do this?

Solution

Microsoft announced a new feature for Azure SQL database that isSQL Database Query Editor. This feature allows us to access Azure databasesand execute queries in a browser. This tool is very handy for simple database operationsin the Azure portal. The most important advantage of the query editor is that wecan execute queries without the need to leave the Azure portal. Say you get a callfrom a client and they tell you about some problems and SQL Server Management Studiois not installed on the nearest computer where you can solve the problem. At thispoint, SQL Database Query Editor can be a life-saver. The fact is that, SQL DatabaseQuery Editor cannot take place of SQL Server Management Studio and it is not a competitorto SSMS. Query editor is more useful for simpleCRUD (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 editorin the Azure portal. Query editor is placed under the database main tab asshown below.

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

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

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

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

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

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

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

Save query: With this option, we can save queries to a localcomputer 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 toexecute 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))

If you have a habit of using F5 to execute queries, you have to be careful becauseif you press F5 accidently in the query editor it will refresh the page and youwill 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 AdventureworksLTsample database for Azure. The following query will retrieve rows to a Results pane. At the end of the result panel you can seethe query status and execution time.

SELECT * FROM SalesLT.Customer

The following query will INSERT rows into a table.

INSERT INTO EditorSampleTableVALUES('Name1','3400'),('Name2','3500'),('Name3','3400')

Things to know when using SQL Azure Query Editor

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

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

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

Or you can enable the option with following system stored procedure in the masterdatabase. 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')BEGINExec sp_set_firewall_rule N'AllowAllWindowsAzureIps','0.0.0.0','0.0.0.0'PRINT 'Allow Access to Azure services enabled'ENDELSEBEGINPRINT ' Azure services enabled before'END

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

SELECT top 1 * FROM SalesLT.CustomerGOSELECT top 1 * FROM SalesLT.Product

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

Usage Experience

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

SET STATISTICS TIME ON; SET STATISTICS IO ONGOSELECT top 1 * FROM SalesLT.Customer

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANUPDATE EditorSampleTableSET UserName='XXXXX'WWHERE ID=1

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

At first this error did not make sense and I did an internet search to find somedetails about this error and could not find any details. Finally I,posted a question andGrant Fritcheyanswered 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 avery handy tool for some basic operations, but it still has some limitations andunsupported features. Also, it is still in preview (Services running in previewmode are made available for testing, evaluation, proof-of-concepts and reportingissues and feedback to Microsoft) version. In my mind, it is a bit early to useSQL Database Query Editor for more complex operations, but it can be useful forspecific operations.

Next Steps

• Read the below article to learn how to create a blank Azure Database or usethe AdventuresWorksLT sample database
  • SQL Azure Create Database Tutorial
• Review moreSQL Server Azure Tips.

About the author