Azure Data Studio or SSMS – which should I use?

Question from the title already became extremely popular and it will become more and more as time passes by. Especially after Microsoft recently decided to include Azure Data Studio in the latest SSMS installation by default!

Since Microsoft made Azure Data Studio generally available in September 2018 and investing heavily in this tool improvement in the meantime, it looks like good old SQL Server Management Studio is destined to go into (well-deserved) retirement…

But, is it like that?

As a SQL Developer, who is writing queries on a day-to-day basis, I will try to give some observations based on what I discovered so far, with special emphasis on Azure Data Studio, since I believe that most of us are more than familiar with SSMS already.

Azure Data Studio

Short intro before I dive deeper into some cool features of the “new kid on the block”. When I heard the name of the new tool, I wasn’t planning to test it deeply, because the “Azure” part didn’t wake special interest in me. However, after I heard Ben Weissman speaking at SQL Saturday Vienna this January when he explained that “Azure” word doesn’t necessarily mean that you need to use this tool in conjunction with Azure palette of products, I decided to give it a try.

So, my first impression is: Microsoft named this IDE quite misleading! Why calling it “Azure Data Studio“, when connecting to Azure is just one of the options? You can also connect to the on-prem SQL Server database, as previously with SSMS, but you can also connect to Big Data Clusters (the latest feature of SQL Server, introduced in 2019 edition).

The main advantage of Azure Data Studio is its portability – while SSMS can work only on the Windows platform, ADS can run on Linux and macOS as well. That’s a huge step forward and going in line with Microsoft’s general expansion to non-Windows world.

Another huge difference to SSMS is that you can use notebooks, and writing SQL, Python, Spark, or Scala scripts within them. I can’t elaborate more on this since I’m still not using this feature (I’m a traditional SQL guy), but it definitely gives more flexibility for more sophisticated usage, comparing to SSMS.

Ok, enough with generic descriptions of the features and differences between ADS and SSMS. I will try to bold some neat features that Azure Data Studio offers:

#1 Object Definitions

When I write queries, I often need to check database object definitions. For example, to see if the underlying data type is Datetime or Datetime2. Of course, that can be achieved in SSMS using some 3rd party add-ons (by the way, I like SSMS Boost), but it’s quite straightforward in ADS:

Simply mark the object you want to check, right-click, and under Peek, select Peek Definition. You will immediately see the definition of the selected object:

#2 Dark Theme

As far as I know, SSMS still lacks dark mode, and I know a lot of developers are craving this. In ADS, dark mode is there!

Go to File->Preferences->Color Theme and choose the layout you want:

Looks pretty cool, ha?

#3 Quickly visualize query results

This can be really useful and I’m using it extensively when working with Azure Data Studio. It can give you a brief overview of data returned by your query, so you can perform quick data profiling straight away and check how many NULLs are there, if there are some outliers, etc.

After I ran this query, I can choose a chart icon on the right and visualize my results immediately:

Moreover, as you can notice in the picture above, I can define the whole set of parameters and adjust the visual appearance of the results.

#4 Source Control

Azure Data Studio comes with a Git source control manager (SCM), so source control becomes one of the greatest advantages comparing to SSMS! More details on how to perform source control of your code can be found in official Azure Data Studio documentation.

#5 Easy export to Excel/CSV/JSON

In many cases, you need to provide your users with quick results of the queries. Now, no need to copy/paste query results or using some external add-ons for processing these simple requests. With literally one click, you are good to go!

On the right side, you simply choose if you want to export your results as CSV, Excel, JSON, XML or to visualize them (as in the previous tip).

#6 Custom Insights/Dashboards

This one could easily take first place, but I intentionally left it for the end. Imagine that you can have all relevant measures in one place, such as the number of currently running queries, deadlocks, index fragmentation, etc.!

I will demonstrate how you can create a dashboard for index fragmentation check, but you can easily expand your dashboard with queries you may find relevant for your monitoring.

SELECT st.index_id
            ,name AS indexName
           ,avg_fragmentation_in_percent AS frgPrct
           ,fragment_count AS frgCnt
          , avg_fragment_size_in_pages AS frgPages
FROM sys.dm_db_index_physical_stats (DB_ID('StackOverflow2013'), NULL, NULL, NULL, NULL) AS st
INNER JOIN sys.indexes AS i ON st.object_id = i.object_id AND st.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent DESC

Once I execute this query, I select Chart icon on the right:

I will save this query on Desktop (you can save it in whatever folder you like). Then, I choose the Table chart type and click on Create Insight above the result set:

Once I do this, the JSON code will be generated. After that, go to View tab, choose Command Palette and then Preference: Open User Settings.

Search for Dashboard.Database.Widgets and select to Edit in settings.json.

In “dashboard.database.widgets” key, paste JSON code created in one of the previous steps.

Save this settings.json file, navigate to StackOverflow2013 database, right-click on it and choose Manage. As you can see, our newly created widget is pinned to a dashboard and we can easily monitor what is going on with our indexes fragmentation.

You can create multiple different widgets and pin them to a dashboard, so you get a quick overview of key metrics for your database.

Final verdict

Despite being a truly an awesome tool, Azure Data Studio still lacks some key features in order to be considered as a direct substitute for SSMS. That’s especially true for DBAs tasks, where SSMS as a mature tool offers many more options.

As far as I see it, both tools are here to stay, at least for some time in the future!

Microsoft constantly upgrades Azure Data Studio, and I will dedicate separate articles to some recently added features, such as SQL Agent jobs, or more common tasks, such as using estimated and actual query execution plans to tune your queries.

Honestly, I still use SSMS more frequently than ADS (maybe just because I’m more accustomed to it), but I believe that every developer should give at least a try to an Azure Data Studio.

Last Updated on October 30, 2020 by Nikola