I was recently at Azure Data Fest at the Boston Microsoft MTC Campus, and one of the tools they highlighted was Azure Data Studio. I took a spin through the tool afterwards and wanted to share what I found to be 5 of the most exciting features in Data Studio.
Before diving into my 5 – I want to preface this by saying that at the moment, Data Studio is not a complete replacement of SSMS. There are definitely missing features (profiling is in preview, SQL Server Agent is in preview, etc). However, over the coming year Microsoft has indicated they will continue to update the tool. So for now, it will act as an addition tool in your toolbox, rather than a complete replacement of SSMS.
You can download and view the documentation here: https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-2017
Alright, let’s get into it!
1. ‘Peek’ at Definition:
Often when writing queries, you need to see underlying table definitions in order to check out datatypes, see what columns exist, etc. Normally this involves scrolling through the list of available tables to find the definition, which can be cumbersome in databases with thousands of tables. However, in Data Studio, you can simply right click and select ‘View Definition’, and a modal pops up showing the table definition. This is a nifty little feature that I will definitely use!
2. Quick translation of queries to charts
The limits with this feature are pretty extensive. There are many instances where I need to quickly get a some information to a business user, and have to first query the data, and then pop the data into another tool to create a graph. Now you can do this pretty easily, all at once! It is also useful when analyzing a dataset for the first time. You can quickly see how many nulls do certain fields have, check out record distribution across certain categorical columns, etc.
3. Built in Source Control
I have HUNDREDS of queries that I use for day to day analysis and tasks, but are not technically part of a code base. These are all saved on my desktop. I do regular backups, but these queries are not saved in source control in any way, and I would be pretty devastated if I lost all of them. With built in Source Control, I can easily create a folder where I save all of these queries, and quickly check them in right through the UI. As a result, I will never lose these scripts!
4. Quick exporting to Json / Excel / Csv
Many times a business user will want an extract based on a query – with one click I can now easily extract this and send it along. Same goes with extracting to JSON, where you can quickly send JSON format data to any users / processes that require JSON formats.
5. Custom Insights and Dashboards
I think this feature has the most potential out of any of the features in Data Studio. One use case that jumps out is a performance dashboard – where you can see number of active queries, any deadlocking issues, or any sort of custom query you have, and have those all on one customized Insight Dashboard. Many of these features are available in the Azure portal, but Data Studio allows for full customization, which is paramount for custom systems.
Another case could be data quality checks – you can write some simple queries that do quality checks on your data, and display any bad records / results in a single place. This is a quick and easy way to set up monitoring with little runway time.
One thing I hope to see in the future on this platform is SQL queries in a ‘notebook’ form, similar to how python works in Jupityr notebooks. I often have to query cross servers, and end up with many windows open. If I could have one Notebook with different tiles that point to different servers, that would be an awesome feature to organizing queries and presenting business cases to users.