By: Esat Erkec | Comments | Related: > 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.
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 login name and password and then click OK. After a successful login, the query editor screen will appear.
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.
Login: With the login button, you can change your connection type and credentials (user name, password).
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.
New Query: When we click this option, it opens a new query tab in the query editor screen.
Open query: With this option, we can access and open SQL query files which were saved before.
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) )
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
The following query will INSERT rows into a table.
INSERT INTO EditorSampleTable
VALUES('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 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.
This option is placed in the Azure SQL databases firewall settings and you can enable or disable this option.
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.
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
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
Everything seems to be fine. Then I tried to commit open transaction and I got an error as shown below.
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
- Read the below article to learn how to create a blank Azure Database or use the AdventuresWorksLT sample database
- Review more SQL Server Azure Tips.
About the author
Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
