Getting Started with Azure Data Studio
Azure Data Studio is a tool used not only to handle Azure SQL, Synapse or Azure Managed instances, but also, it is used for SQL Server on Premises, to handle PowerShell code, handle PostgreSQL databases, program in Python, Spark R, MongoDB, create SandDance charts and more.
In this tutorial, we will provide an introduction to installing Azure Data Studio, how to create a SQL Server table, add some data and create a Notebook in Azure Data Studio. This article is an introduction to the Azure Data Studio user interface for data professionals.
- SQL Server installed
- Azure Data Studio can be installed on Windows, Mac, or Linux. In this example, we will install it on a Windows machine.
Azure Data Studio Installation
First, go to the Azure Data Studio download page and look for the Azure Data Studio installer. In this example, we will download the Windows User installer.
The installation is straightforward. First, you have the License Agreement. Select I accept the agreement and click Next.
Select the installation folder and click Next.
Select the Start Menu Folder and click Next.
You can set up these additional items as shown below. Click Next.
If everything looks good, click Install.
Find Azure Data Studio Version
Launch Azure Data Studio and go to the Help menu and select the About option. At the time of writing this article, the latest version was 1.39.1 as shown below.
For more information about Azure Data Studio versions, refer to this link Azure Data Studio releases. Azure Data Studio is open-source code available on GitHub.
Connect to SQL Server database on-premises using Azure Data Studio
To connect to a SQL Server database on-premises, click the Connections icon (1) on the left and then click the New Connection icon (2) on the right to add a new connection.
We will use the Parameters option to create the connection as shown in the image below.
- Connection type - select Microsoft SQL Server.
- Server - enter the SQL Server name or you can use a period for the local server.
- Authentication type - can be Windows Authentication or SQL Authentication. For more information about authentication types, refer to this link: How to check SQL Server Authentication Mode using T SQL and SSMS. In this example, we will use Windows Authentication.
- Database - select the database you want to connect to on the server.
- Server group - this lets you add servers to different groups for easier management.
- Name - this lets you provide your own name for the connection.
After entering this info, click Connect.
If everything is OK, you will see the connection was created.
Create a table in Azure Data Studio
Once connected, if you open the server tree, you will see Tables. Right-click on Tables and select New Table.
In this example we will create a table named actors with 3 columns:
- Id - type int
- Name - type nvarchar(50)
- Lastname - type nvarchar(50)
Note that a script is generated with the T-SQL code.
Make sure to check the Primary Key checkbox for the id.
When you are finished, click Publish changes icon to create the table.
A Preview Database Updates message will be displayed with the create table action. Click the Update Database button.
To check that the table was created, right-click on the Tables node and select Refresh. The dbo.actors table should be displayed
Edit data in SQL Server tables in Azure Data Studio
Now, we can add data to the table. Right-click on the dbo.actors table and select Edit Data.
Add some data and then click the Run icon to save the data.
Running T-SQL Commands
Right-click on the SQL Server connection and select New Query to access the SQL editor.
In this example, we will use the sp_who system procedure to get the list of processes running. Type sp_who and click the Run icon to see the query results.
You can also create database objects using T-SQL in Azure Data Studio in the query editor. Here is code to create a simple view. Enter the code and click Run to create the view. Here is the syntax:
CREATE VIEW vActors AS select id, name, lastname from dbo.actors where id=1
Create Notebooks in Azure Data Studio
A Jupyter Notebook is a web application used to share your code including documentation and narrative text. Notebooks are popular for developers and it is functionality that we cannot do in SQL Server Management Studio (SSMS).
In Azure Data Studio from the menus select File > New Notebook.
You have different Kernel options like Python, SQL, R Spark, and PowerShell. In this example, we will work with SQL.
Click the Code link to add code.
We will add some code in the Notebook.
Execute the code, by clicking on the run icon.
Note that you can see the code, and the results. Now, select the Text cell to add some text to the code.
You can add fonts and links to your code to provide additional information. Below is an example of text I added. When this is run, it will show the text underneath the query output.
- Azure Data Studio is a great tool to handle not only Azure resources but also administrative tasks for on-premises servers. Please read our article about Popular extensions available on Azure Data Studio for more information about database administration extensions.
- If you are a DBA that works on SQL Server on-premises you should continue using SSMS which is more robust, but you can use Azure Data Studio to work with PowerShell scripts and database tasks.
- If you need to use Azure SQL Database, MongoDB or PostgreSQL, Azure Data Studio can help since it is cross-platform.
- Azure Data Studio is also a good option if you use Linux or Mac because there is no SSMS installer for those operating systems.
About the author
View all my tips
Article Last Updated: 2023-01-05