Review: SQL Operations Studio (Preview) for SQL Server
As someone who develops on SQL Server every day, I wanted to try it out the latest gizmo, SQL Operations Studio, or SQLOps. I hoped it was going to be my new favorite toy with all those extensions. In the end, I’m fine with it. There is one problem and a couple of missing features, however, that will keep ETL developers using SSMS.
What Is It?
SQL Operations Studio (SQLOps) is the new lightweight database development and operations tool. This particular version of SQLOps is a Preview – not the official release. The idea is that you can use it instead of SQL Server Management Studio. It is built to be cross platform for both Azure and your local servers. SQL Operations Studio is built on top of Visual Studio Code, another lightweight, cross platform interface that focuses on the more typical coding side, such as for C#, Java, and a surprisingly high number of other languages. The SQL Operations Studio version is configured toward SQL Developers and DBAs.
Things I Like
Command Palette (Ctrl+Shift+P)
The command palette gives most things a kind of command line interface of sorts, where you type in what you want, the command is auto-completed, and you’re off to the races. When you hit Ctrl+Shift+P, it will bring up a box you can type in, and it will kind of guide you to where you want to go. Try starting off with “?” to get some help. “>” is usually what you want as a first character to start entering commands. Both of those are characters you can type in yourself to use the different options.
Extensions (Ctrl+Shift+X)
There are a couple of ways to get extensions through the Extensions interface. You can download vsix files and install them through “Extensions: Install through VSIX”. You can also simply click, “Install” next to the name of the extension, and SQL Operations Studio takes care of the rest.
In the Extensions tab, there is a star on the top left corner for suggested extensions. This thing is made for extensions, so go nuts.
Keyboard Shortcuts
Can there be enough Keyboard Shortcuts? I don’t think so. It’s very keyboard-centric. Almost everything you will want to do has a keyboard shortcut in the menus and features. If you don’t find what you want, there is a chance that you can create the binding yourself. There are also extensions that you can add to SQLOps for key mappings.
For a list of keyboard shortcuts, use keyboard shortcut [Ctrl+K, Ctrl+S]. That’s two separate key presses. You can also use the command palette [Ctrl+Shift+P, >Preferences: Key] to see more.
Git Integration (Ctrl+Shift+G)
Git just works, and it’s nice. If you open a folder that is under source control, it will plug it into SQL Operations Studio’s source control tab. The Git commands available through this interface are better than the commands I’ve seen through other Git plugins for Visual Studio, as well. For example, after using the usual Git plugin for Visual Studio 2015, I quickly installed TortoiseGit instead. Looking at the command options available, I don’t think I’m going to have to do that.
Most SSMS Editing Capabilities Still Available
If you have enjoy being able to edit your SQL using the Regular Expressions capability found in the Find/Replace in SSMS, good news! It’s still there. You also still have the ability to Column Select using Alt+Shift, skipping around and putting cursors with Alt+C, etc.
Auto-Completion
Yes, SQL Server Management Studio will auto-complete, as well. But I haven’t had as consistent a response from SSMS as I have with SQL Operations Studio. SQLOps is very good at auto-completion.
Things I’m Like…Eh
Speaking of Auto-Completion… Can an argument be made for a template form for creating a table, view, or procedure? Absolutely. Do I like it? No, I do not. But, it’s fine if that’s what you like. Sometimes it can be helpful when you haven’t made a function in forever to have something like that to help you out.
It is interesting the way that this is done: as an auto-complete. In the text of your file, you just start typing “sql” and a dropdown of possible actions will appear. Try it and you will find you have several options. As is usual for auto-complete, the more you type, the more your selection is narrowed down. Interesting is that off to the right, you can see a preview of what you will get from the autocomplete.
If I continue typing, “sqlcreateta”, I will see the following. Selections on the left, preview to the right – that’s pretty cool. You can even scroll down in that example on the right to see the rest.
I will admit, this may be something that grows on me. At the same time, the list of available auto-completes available aren’t really something I don’t just knock out on a regular basis. Ironically, considering my example earlier, functions are not on that list. Neither are views, or merges.
We also have to remember that this is a preview version, so maybe there is more to come in this department.
Things I Don’t Like
Filtering
You can’t filter objects – there’s no right-click > Filter abilities. If you work on databases with multiple schemas like I do, you’re going to miss that, I think.
Arrow Keys to Expand/Collapse Trees
Normally, if I’m way down in the bowels of views and I want to quickly take myself back up to the database level in the Object Explorer, there’s no problem. I just Hit the Left Arrow until everything is collapsed how I want it. Or, I can expand folders with a Right Arrow. For an interface that boasts heavy keyboard interaction, um…. WTH?! This is basic windows interface stuff.
No Double-Clicking Errors to Go To the Error
You can’t double click the errors to be teleported to the offensiveness. But! It does tell you the line number, and the line numbers are shown.
No Way to Tell How You are Logged In
In SQL Server Management Studio, you can see in the Object Explorer which account you used to log into which database. In SQLOps, you cannot. As long as you ever only use one login, this shouldn’t be a problem. I wish – I truly wish – that was the case for me. In my job, I could be logging in using one of four different logons. Each SQLOps or SSMS instance is opened as a different user, using those accounts, but in SQLOps, I can’t tell which is which.
No Access to Integration Services Catalog
This is the big miss for me. There is no way to access the Integration Services Catalog so you can see execution reports. I can get to SQL Agent, which tells me when a job fails. But if my job is running a package out of the SSIS catalog, the job error is going to refer me to those reports. So far, there is no extension for this that I can find, which means I must open up SQL Server Management Studio to get the information I need. I will never be able to leave SSIS for SQLOps as long as that is the case.
What’s it Going to Cost Me?
SQL Operations Studio is free! No problems there, just help yourself. Download link is below in the references.
Bottom Line
SQLOps is currently a Preview, not yet full production. I think there are some very useful tools in SQL Operations Studio. These tools could appeal to both developers and DBAs. Will I continue trying it out? Sure. Is it my new favorite toy? Not really as I lose some functionality, but it could be fun finding extensions and seeing where this goes.
References