Why SSMS is still king over ADS and Fabric Explorer?

I work with a lot of teams of folks with 10-20 years experience of TSQL development and one common factor is they all still use use SSMS over Azure Data Studio (ADS) and the newer web based Fabric Explorer and script editor.

The web experience has put a lot of effort into features like intellisense and by all accounts its done a good job there

So why is SSMS still king ? Well I’ve attempted to name a few reasons for both editing view, sprocs and scripts.

1) Dont fix whats not broken

SSMS has reached a maturity where developers are “almost” happy with it. For Web or ADS to overtake, it must “add” value, not just be “almost as good”.

Some areas to add value are source control integration, co-pilot, auto-linting and native support for VS projects, which has traditionally been very behind the curve in SSMS.

A web editor is going to appeal to casual users who dont want to install SSMS or mac users, but not as much to those who already have SSMS and work with it. Hard core SQL devs and nerds (like me)

2) Support for Large Projects and EDW

Both the web editor and ADS really only work with smaller projects. When we work on EDW projects with 1-10k of objects they just crash. SSMS gets around this by having filters to filter by schema, or object name. Super critical if you ever look at tables in an SAP instance…

3) One Tool for all of SQL

During a typical day, I switch between connecting to SQL in IaaS, SQL in dedicated pool, SQLDB and Fabric,

Sometimes I am migrating from one to the other and it just makes sense to be able to connect to the entire SQL family with a single tool.

We can even use tools like registered servers to run queries across different SQL Servers to check meta data or queries in sync. below shows a query running across on-premise, fabric and SQLDB

4) Responsiveness

Below is a basic trivial query running in SSMS. 28 ms to execute and 1,239 ms in Fabric web client. That is 44 times slower. When we work in SSMS we run queries at high speed like a touch typist in tight iteration circles and wait time is so much more on the web interface.

code is just faster in this sequence Web=>Java->Non Java

5) The messages are too verbose.

The messages below are completely gratuitous. We can see the execution time bottom left so dont need 1/3 the screen taken up with bland information which doesnt add value.

When we have SET NOCOUNT ON we want to see zero messages other than PRINTS and errors.

6) Lack of Performance Tooling

SSMS has a wealth of of tools for performance. Ctrl-L for the query plan is everyone’s bread and butter. In the web editor this is repurposed to select a line ;-(

Now this isn’t a diss on the web editor, as Fabric doesnt yet give graphical query plans even in SSMS. But when it does, we want Ctrl-L – old school.
Why stop there. Can we get a form of SET STATISTICS IO ON, live execution plans and actual plans for Fabric. I dont care if its spark plans, just something we can drill into where time spent and what tables grabbed the most IO or time.
All this performance data should have a button or hot key to produce instantly or the option to send with the result much like SSMS client statistics. We are lazy and dont want goto a separate window and run lots of DMVs to see performance of what we have in front of us.

7) Lack of Extensibility

I dont have stats, but Im pretty sure that VS Code is Microsoft’s most successful IDE ever. One of the big reasons is the sheer extensibility and availability of plug ins. Despite this, It never quite overtook SSMS as the SQL aspects was always a bit clunkier, but for script languages like Powershell and Python it rules the roost.

SSMS does support plug ins, but not as wide with Red Gate and few others like SSMS Tools and a few more being popular.
What can we plug into the web editor – nothing.

8) Weird Save Logic and sprawl of files

95% of the queries I run in SSMS I dont save. Its an interactive tool for running queries in the moment and sometimes I have the odd query I want to save and then I either wrap it in a sproc, or save to GIT controlled folder.

In the web editor for Fabric 100% of queries are saved. In my typical working day, this would create 1000s of files. Even worse, there is no concept of a folder structure so its almost impossible to organise the sheer volume of files. In the end we give up using “My queries” due to the clutter.

The shared queries are super cool though. Currently using them to store say common deployment or config scripts across teams. They too could do with navigation folders.

As a suggestion when we start querying this should be a single “scratchpad” which could be persisted but we need to forcibly name a query to permanently archive it and we need folders ASAP.

9) More drag and drop support

Within SSMS I’m forever wanting to write a query that selects ALL columns, but by name, not with a *. I can just drag the columns over to IDE and it adds them all – nice!
In the web editor we can only drag tables names. Not quite as useful

10) Windows Windows Windows

Im a multi tasker. I will have three screens with SSMS on each screen and then inside SSMS I will have split windows to compare different versions of the same query, or the same query running on different environments.

11) Accessibility

Being old, my eye sight isn’t great. SSMS can zoom fonts of just the query windows while maintaining explorer and results. It also has lots of options for presenter mode like
https://blobeater.blog/2017/02/22/ssms-presenter-mode/

The web editor and explorer just cant compete with this with just the default browser zoom which scales everything including menus.

12) Object Explorer Details

I’m a big fan of object explorer details. It lets you view extended attributes. Like when a view was created, last ones created, who created them, row counts in tables, etc
The biggest thing by far is being able to shift click and select multiple objects for an operation like a drop.

What is cool about the web Editor ?

Co Pilot is making a lot of noise, so watching this space to see if that helps with adoption. To date LLM has been wildly helpful to us with Python, Regex and PySpark, but not used at all for TSQL.

The live shared script is really nice and is something that SSMS is will struggle with being a client tool.

What is SSMS Missing ?

The three biggest ticket items for me (or any SQL Editor) are:

• Version Control. Being able to check in and out sprocs and view history. SSMS never quite got there and 3rd party tools like Red Gate made a killing.
• Schema Compare. We always want to compare a table between DEV ad TST. Im a big fan of VS Schema compare. What would be even cooler would be if I could right click on a few tables and select “compare to PRD”
• Data Compare. With the advent of meta data, data is now code. So we want to compare some tables between dev=>tst=>prd, script changes and apply them.