Azure offers many features and services, so if you’re new to Azure and the Azure Portal there is a lot to learn. One feature in the Azure Portal is the SQL Database Query Editor. Here I’ll teach you how it is possible to execute queries in the Azure Portal when we don’t have access to SQL Server Management Studio (SSMS).
The SQL Database Query Editor allows us to access Azure databases and execute queries using a browser, which is a handy tool for simple database operations in the Azure Portal. The biggest advantage of the Query Editor is that we can execute queries without having to leave the portal itself.
SQL Database Query Editor can be a lifesaver in situations where you’re asked to solve a problem, but SQL Server Management Studio (SSMS) is not installed on a nearby computer. The Query Editor can’t take the place of SSMS, nor is it a competitor. But it’s a great option to have and if you’re familiar with writing queries in SSMS, you’ll feel comfortable writing queries in the in-browser Query Editor.
Many common queries can be run in the editor and you have flexibility to run partial or batch queries. By using syntax highlighting and error indicating, writing scripts is a breeze and the editor is useful for simple database CRUD (Create, Read, Update and Delete) operations.
Before executing any database, you must connect to a database by logging in with either your SQL Server or Azure Active Directory credentials. In my brief demo, I’ll show you how to connect using Query Editor in the Azure Portal as well as some things to know when using this feature.
- You’ll find the Query Editor under the database main tab. To locate this click on a database and you’ll see it in the left side pane, or you can do a search for it in the search bar.
- Click on the Query Editor and in the connection settings screen the Query Editor allows 3 kinds of authentication: SQL Server authentication, Active Directory password authentication or Active Directory single sign-on.
- In my demo, I use SQL Server authentication. Once logged in you’ll see the editor has a very basic design. On the left you’ll see the object explorer which you can expand the tabs and see all tables, views and stored procedures.
- The toolbar at the top has buttons for:
- Edit Data, where you can edit data in a table. Being a data scientist, I don’t really recommend this but there may be a testing scenario where you may want to alter the data.
- New Query for adding queries.
- Open Query for browsing your system for queries you’ve already written.
- Now I’ll write a couple queries to show some of the functionality there (see demo).
- My first query is select top 100 from my claims table. If I run that it will return the top 100 rows in the Results pane. I can also see what I’ve done in the Messages tab.
- When you use Open Query and open previously written queries, they will open in the Query pane so you can run within that. We also can create tables there.
- In my example I create a simple table with a primary key and a couple of columns (user name, favorite location). To run this, I highlight only the code block I want to execute and in messages I see that it succeeded with 0 affected rows.
- To ensure it succeeded I highlight select * from dbo.AEDSampleTable statement and run that so I will see results (none in my case) in the Results pane.
- Then I want to add a couple rows into that table, so I highlight INSERT INTO and VALUES and execute that query. This is a bit different than standard SQL (see demo).
- When I run the select * from dbo.AEDSampleTable again, I can see those rows were added in my results.
- As I don’t want this table in here, I can simply highlight my DROP TABLE statement, execute and run that and when I refresh, I will no longer see that table listed in my Object Explorer.
I hope you found this helpful if you’re just starting out using the SQL Database Query Editor within the Azure Portal. If you have more questions on how to utilize this feature or about anything Azure related, you’re in the right place.
Contact us or click the link below – our expert team is here to answer all your questions about Azure and using your data in the cloud to grow your business. Let us help you take your business from good to great.