Azure Data Studio: SQL Server Tools for Linux, Mac, Oh, and Windows Too

28/02/2023

What do you do if you think the name "SQL Operations Studio" isn't quite exciting enough? Well, if you are Microsoft, you lead with the word "Azure," as in Azure Data Studio.

Don't be fooled by the name; The Azure Data Studio is not just for Azure databases. Not only is the Azure Data Studio (ADS) useful with all instances of SQL Server wherever they might be, but ADS is available for Windows, Linux, and Mac. Developers will instantly recognize it as a direct descendant of VS Code, Microsoft's cross-platform alternative to Visual Studio.

The Azure Data Studio is easy to use, but make no mistake about it; it's not the Management Studio. While the core functionality is there, lots of SSMS features are missing. But there are a few new features found only in ADS that SSMS folks might envy.

The Command Palette

One of the first differences of ADS to get used to is the Command Palette, a comprehensive tool that is part command line, part menu. Although many ADS functions can be accessed through menus and buttons elsewhere, the Command Palette is the master list of ADS commands. The Command Palette can be launched from the View menu or by hitting <ctrl><shift>P.

Connecting to a Server and Executing Queries

(click image for larger view)

Connecting to a Server and Executing Queries 02

(click image for larger view)

Connecting to a server brings no surprises. While writing a query, one does notice, however, that the ADS implementation of intellisense is far inferior to that of SSMS. For example, it does not seem to pick up on table aliases while you are typing a query.

Connecting to a Server and Executing Queries 03

(click image for larger view)

Execution Plans

The "Explain" button in the toolbar provides an estimated execution plan, but how do you access the actual plan? Unclear until you remember to go back to the Command Palette for anything you cannot find. Once you find "Run current query with actual plan" you'll also be reminded that <ctrl>M will do the trick (same as in SSMS).

(click image for larger view)

Charts

While the Azure Data Studio is not intended to compete with the charts from available reporting and analytics applications, there are many times when all you want is a quick exploratory look. Importing SQL Server data into Excel or Power BI Desktop would be overkill in such cases; ADS provides a simple charting capability to let you take a quick graphical glimpse of the data you have just queried.

Unfortunately, at present, the charts are too primitive, and the options too limited to be of any greater use.

(click image for larger view)

Notebooks

I am a big Jupyter fan; integrating notes and code into a single document solves lots of problems for me. I was, therefore, pleased to see that the Azure Data Studio includes a Jupyter-like notebook capability. Perhaps I was expecting too much, but the ADS notebooks are a pale reflection of actual notebooks. SQL code can be stored and executed in a notebook, as can PySpark and Scala if you are evaluating the new Spark features of SQL Server 2019.

Unfortunately, only the blandest features of Jupyter markup are supported. For all practical purposes, you have section headings, and you have the primitive layout and formatting features of HTML available to you. The ADS notebook seems to have been designed with more features in mind, but they either aren't there or aren't working yet. Still, the ability to document queries and results, all in a single document, is an immense leap forward for the SQL developer.

(click image for larger view)

Conclusions

SQL Server developers and administrators who are currently using the SQL Server Management Studio have no reason to switch to the Azure Data Studio. On the other hand, Linux-based administrators might find it helpful, though perhaps not so much as SSMS on a Windows workstation would be. Nevertheless, the current incarnation of the Azure Data Studio seems to be a step in the right direction. And options are always good, even if sometimes the classic option still remains the best choice.

Azure Data Studio FAQs

What is Azure Data Studio?

Azure Data Studio is a tool developed by Microsoft that is used for working with SQL Server databases. It is available for Windows, Linux, and Mac.

Is Azure Data Studio only for Azure databases?

No, Azure Data Studio is not limited to Azure databases only and can be used with all instances of SQL Server.

How does Azure Data Studio compare to SQL Server Management Studio (SSMS)?

While Azure Data Studio has many core functionalities of SSMS, it is missing many SSMS features. Therefore, it is not a replacement for SSMS, but it is a useful option, especially for Linux-based administrators.

What is the Command Palette in Azure Data Studio?

The Command Palette in Azure Data Studio is a comprehensive tool that is part command line and part menu. It is the master list of Azure Data Studio commands, and it can be launched from the View menu or by hitting <ctrl><shift>P.

How do you access the actual execution plan of a query in Azure Data Studio?

To access the actual plan, you need to find "Run current query with actual plan" in the Command Palette, or use <ctrl>M.

What is the charting capability in Azure Data Studio?

The charting capability in Azure Data Studio provides a simple way to take a quick graphical glimpse of the data you have just queried. However, the charts are too primitive, and the options too limited to be of any greater use.

What is the notebook capability in Azure Data Studio?

The notebook capability in Azure Data Studio is similar to Jupyter, allowing for the integration of notes and code into a single document. SQL code can be stored and executed in a notebook, as can PySpark and Scala if you are evaluating the new Spark features of SQL Server 2019.

How can I get certified in Azure Data?

First, master the basics with Learning Tree course 8586, Microsoft Azure Data Fundamentals Training (DP-900). Then continue on to a number of Data Associate and Data Specialist Certifications, including Learning Tree course 8576, Administering Microsoft Azure SQL Solutions DP-300 Certification Training. While requisite certification is not required before taking DP-900, we recommend all Azure Data Fundamentals students start with essential knowledge of computing and Internet concepts and an interest in extracting insights from data.

You should have experience using a web browser, such as Microsoft Edge; familiarity with basic data-related concepts, such as working with data tables in a spreadsheet and visualizing data using charts; and a willingness to learn through hands-on exploration.

This piece was originally posted Oct 12, 2021 and has been reposted with updated formatting and FAQ.

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.