SQL Operations Studio Vs (good old) SSMS

In the following post we’ll go over a “short” comparison between the most popular MS DB management tool SSMS and the new tool provided by Microsoft – SQL Operations Studio. We will go over each tool’s “strengths” and “weaknesses” in an objective fashion as much as possible.

In November last year Microsoft have launched a new lightweight cross platform tool for SQL Developers and DBAs called as ‘Microsoft SQL Operations Studio ‘. It vividly resembles Visual Studio Code perhaps because it was forked from it. Also, both tools are designed not only for Windows but also for Mac or Linux machines and are free of charge.

Does this mean that good old SQL Server Management Studio will be replaced by SQL Operations Studio and in time even forgotten?

It is hard to answer determinately to the above questions as it depends on the needs of the developer using the tool and the system. However, the following comparison between the two tools will be taking into consideration the daily struggles of a Database Developer (not DBA as the needs there are a bit more specific).

Firstly, let me exclude the major difference between the two tools – if you are working under Linux or Mac and don’t want to use a VM you don’t have a choice here. But for Windows OS users SQL Operations Studio also contains a lot of hidden gems that would ease your workload and is getting new functionalities way faster than expected, so I’m in a hurry to write this blog post before it gets outdated .

Also, I presume all readers here have some experience with the SSMS tool (regardless of the version) and little to no experience with Microsoft SQL Operations Studio.

So, to start with, the thing that I liked most about the new tool is the Black Theme (finally!!!). It was about time DB developers could use some tool that wouldn’t make them cry every evening after a long day in front of the monitor. Currently SSMS has a black theme but it needs to be manually enabled and doesn’t really work as expected:

Versus:

Looks way better to me!

Another important upgrade at least for me is the more responsive IntelliSense. SSMS’ IntelliSense has gone a long way to be actually working and useful but the SQL Operations Studio one beats it!

As a developer I’m used to SQLPrompt – awesome tool from Redgate! SQLPrompt can auto generate your whole script, including keywords and stored procedures’ executions. I consider SQL Operations Studio’s IntelliSense to be significantly better than SSMS (as a note it still has a long way to go to be a match for what Redgate offer as a paid tool).

Other fancy new tweaks that I’ve come across are hovering over “Select *” that shows you all columns with their data types and allows you to COPY them. The peek definition feature also allows you to get the script of the object you want (doesn’t work for system objects though). Just to mention I’ve used both with SQLPrompt but unfortunately unless you’ve paid your license you cannot use it in SSMS.

Here is a peek of peek definition:

And the column list while hovering over the wildcard symbol:

However, if we connect to an Azure SQL Database these don’t seem to be working. You do get a hint for the reserved keywords but nothing more ?.

I am also happy to see there is an “Execution plan” in SQL Operations Studio called “Explain”:

Probably not as extensive as the one in SSMS but it could be further enhanced.

There is, after all, an additional tab called “TOP OPERATIONS” that simplifies the investigation into why a certain query performs poorly:

SQL Operations Studio also displays line numbers automatically – i.e. you don’t have to look for the option in the settings. If you must modify the user settings it is quite a pain … there is no GUI and you are modifying the JSON source manually:

Sadly, you cannot also drag and drop object in the Query window, as well as rename objects with double click (if you have the rights, of course).

As we continue with the downsides I need to mention that the various scripting options when right-clicking the DB or object are also missing and there is no Designer for tables which I commonly use.

Another thing I couldn’t find as well is something like the Activity monitor in SQL Operations Studio.

Using ALT key to select a box of items also is unavailable (works in VS 2015).
As for the most commonly used hotkeys the differences are the following between SSMS and SQL Operations Studio:

F5, CTRL + E or ALT + X – Still executes the currently selected code in both studios;

CTRL + R – That shows/hides the Results pane doesn’t work;

CTRL + N – Does open a new query window;

CTRL + L – Display the query execution plan is not available;

CTRL + I – Display the incremental search dialog doesn’t work;

Alt + F1 (sp_help) – Also is not working in Operations Studio.

Only 2 out of 6 – not a good score.

There are many more differences between the two and probably in future SQL Operations Studio will get much more functionalities as well as SSMS. For now, at least for my DB work I will stick to SSMS because of the extra options that are not yet available in Operations Studio but crucial for my work – e.g. numerous hotkeys, administration GUI and scripting options like generate scripts, import, export, etc. Another major reason that should not be underestimated is that SQL Operations Studio is still in preview mode which means that potentially there are a lot of bugs still to be discovered.

Nonetheless, your preferences might be totally different and there may be many other reasons for you to choose Operations Studio over SSMS. If you decide to give it a try please let me know as I am keen to further investigate all the new upgrades that are coming out. I’ll keep an eye on the upcoming updates and will be releasing more blogs on the topic.

References:

https://visualstudiomagazine.com/blogs/data-driver/2017/11/sql-operations-studio.aspx

https://twitter.com/sqldatatools

https://blogs.technet.microsoft.com/dataplatforminsider/2018/01/17/the-january-release-of-sql-operations-studio-is-now-available/

https://visualstudiomagazine.com/articles/2018/01/22/sql-ops-studio.aspx

https://logicalread.com/first-look-sql-operations-studio/#.WnLCKIhuaUk

https://logicalread.com/sql-server-management-studio-10-tips-and-tricks-mo01/#.WnLD74huaUk