Using Azure Data Studio SQL Notebooks

There are a number of database tools for managing and developing databases and it’s interesting to see that, certainty from a Microsoft perspective,  SQL Server Management Studio is often the default tool still used out in the field today.

However, we have also noticed that Azure Data Studio (ADS) is fast gaining traction and has certainly a firm favourite within Adatis for some time.  In this blog, instead of comparing the features of the two Microsoft database tools,  I will be providing you with an option to present your SQL queries in a more readable and structured format.

Azure Data Studio Notebooks provide users with a functional way to read, comment and present their queries. Using ADS Notebooks users can also present their results and store them in a JSON format. The benefits of using ADS Notebooks are the following:

• Rich comments (including links and images), Source Code and Results are all packaged in a single file.
• Notebook files can be combined with Readme and YAML files to construct a book (sometimes referred to as a Jupyter Notebook).
• Notebooks are saved as pure JSON files.
• Can be fully integrated with GitHub.

Creating a notebook in ADS is an easy and straightforward process. We can click one of the main buttons on the screen, choose it from the navigation bar on the left side of the home page, or simply press Ctrl + Shift + B:

Afterwards, we can choose whether we want a “Code cell” or a “Text cell”. We can change that selection anytime. Then we need to make a selection for the Kernel – the programming language we would use in the Notebook. The options are: SQL, PySpark, Spark | Scala, Spark | R, Python 3 or PowerShell. Have in mind that we can only have one Kernel per Notebook. Of course, we also need to establish a connection to a database. In terms of the connections, Azure Data Studio can connect to on-premises SQL Server, Azure SQL Database, PostgreSQL, and even with data platforms like SQL Server 2019 Big Data Clusters. For the sake of illustration, let’s choose a “Code cell”, a SQL Kernel and our well known AdventureWorksLT Azure SQL Database:

Now let’s write and execute the following simple query: SELECT *FROM [SalesLT].[Product]

We can see that the results are being displayed in the same cell, with the option to be saved in a CSV, Excel, JSON and XML format, which I find quite neat and handy. Moreover, we are also provided with the option to view the results as a chart. For the Databricks fans, you will find that the cell options are similar:

What I find particularly useful in ADS, is the IntelliSense. This built-in code completion tool saves a lot of time when we want to create a stored procedure, for example. We can simply type “sql” and the pop-up window will appear with the available suggestions:

When we choose to create a stored procedure, the built-in code snippet for the stored procedure will appear:

Using ADS SQL Notebooks we can have all the queries with their results grouped into cells, which makes it easier for developers to follow the sequence of our code. These Notebooks allow even non-technical users or clients to see the results of the queries in chronological order and export them in more formats than simply a CSV. Nowadays, it is not only important how accurately and precisely we write the code, but how we structure it and present it as well.