By Riley Major, 2018-04-10

For this month’s T-SQL Tuesday, Jens Vestergaard asks us to write about our essential SQL Server tools.

I must admit that Microsoft SQL Server Management Studio (SSMS) is where I spend most of my development time. I haven’t tried the cool kids’ new SQL Operations Studio. And I haven’t spent much time with SQL Server Data Tools. But Microsoft has come a long way from Enterprise Manager and Query Analyzer and honestly from a feature perspective, SSMS is fine.

From a performance and reliability perspective, it leaves a little to be desired. Sometimes, when I switch among the tabs, the query text doesn’t change until I scroll or otherwise cause it to refresh the window. Often, when switching from one tab or window to another, it will freeze for several seconds with high CPU use. It occasionally dies outright (which is thankfully mitigated by its reloading of auto-saved tabs when it’s restarted). And I’m frequently hit with Out of Memory errors, which prevent any additional queries from returning results, despite having gigs of available physical memory. I end up having to open a new instance of SSMS.

But since SSMS is now a separate download, they’ve been able to update it more frequently and supposedly the memory issues are addressed in the latest version (because it’s now large address aware). And unlike many other editors, it has a tremendous block selection feature.

But there is something which provides continued frustration for me personally. In my workflow, the tab titles are just about worthless.

We don’t have automation which synchronizes file changes with server objects, so when I’m editing a SQL Server object like a stored procedure or function, I’m adjusting the server’s object directly. In SSMS, that amounts to executing some sort of ALTER statement. That means that instead of hitting control-S to save– something which is easy to type and an ingrained habit– I have to hit F5. Although I could probably map control-S to F5, I want the editor to be smart enough to understand not to run whatever snippet I happen to have highlighted, but rather execute the object alteration. I understand the challenge for SSMS, since the editor is for arbitrary T-SQL which might contain object alteration mixed with other commands (separated, e.g., with “GO”). But I would like it to have a smart editing window which was limited to object editing and saving. This would allow a safe control-S for save and, even more importantly, it would make the “dirty” (or “modified”) asterisk notation meaningful.

Currently, all of my tabs are always dirty, because I’m not saving any files but rather executing ALTER commands. And again, because all of the tabs are unsaved files, the tab names are uselessly incrementally numbered. When I edit a procedure or function, I would love if SSMS opened a tab designed specifically for object editing, with a modification indicator that corresponded to whether I’d edited the object since the last control-S save.

I tried using a free third party tool for this behavior, Devart’s dbForge, but it was always a little quirky. For example, it didn’t report all types of errors encountered during saving. Sometimes it would give generic failure text, which didn’t even point out the offending line number. And one version had a serious flaw. It would put all of the edits in one instance into some sort of a transaction. If you encountered an error in saving one object, it would leave the transaction open. Eventually, if you fixed the original error, you could use other windows to edit other objects, referring to the seemingly successfully edited original object. But if you closed the apps, everything reverted back to before the first error. So you think everything was saved properly but nothing was. That was infuriating (though they say they fixed it after a few weeks). So back to SSMS I went.

But I don’t have to content myself with its current capabilities (or the occasional whining here). The SQL Server community has created a Trello board for SSMS Enhancements and Microsoft has a new official feedback system. (Someday I will rant about the termination of the flawed but useful Connect system.) I’ve already created a card for this object editor window idea, along with some others.

But I’ve talked about SSMS improvements before and this post is supposed to be about other tools.

So here they are:

• Red Gate SQL Compare – Especially if you don’t have some sort of automated system which synchronizes your SQL Server objects with source control, you really need a schema comparison tool. There are a variety of options, and some Microsoft tools can do it for free, but I’ve been using Red Gate’s tool for years and it’s always been reliable and user-friendly.
• Beyond Compare by Scooter Software – Being able to compare folder trees and synchronize their files is critical, especially if you deploy code the old fashioned way, without an automated system. Beyond Compare allows you to quickly see any file differences and then drill into a particular file to review the changes. It allows you to filter out files and folders you don’t care about and specify how comparisons should detect differences. It’s so useful I’ve purchased it for personal use. And they recently made a Mac version. This is one of my favorite programs.
• Red Gate SQL Search – If you’ve ever had to refactor anything in T-SQL, then you’ve needed a tool like this. Perhaps you searched your code manually, object by object, or scripted everything to a file and used a text editor, but no matter what, you need to search through SQL objects. And this free tool makes it fast and easy. Anyone who uses SSMS should install it.
• Agent Ransack by Mythicsoft – I’ve always found Microsoft’s built-in search to be terrible. And any time you change a procedure name or function parameters, you have to fix everything which calls it. Which means first you have to find it. Agent Ransack allows you to specify file datetimes, name fragments, and, most importantly, contents. So search your code directory for that procedure name and you’ll find every reference to it in seconds. There are probably many other tools which accomplish this goal, but I’ve used Agent Ransack for years and it’s always worked quickly and accurately. And hey, it’s also free.

Perhaps you can spot a pattern. The most important tools, in my opinion, are those which help you find code you need to change, and then deploy the code you’ve changed– either inside or outside of SQL Server.

Update 2018-04-11: I realized thanks to Glenn Berry’s comment that tab coloration by server is a Redgate SQL Prompt feature. I removed reference to it in the post. Sorry about that.

There is a way to set not the tab but rather the status bar color by creating registered server. Right-click on the server in the Object Explorer and click Register. Then click over to Connection Properties, check the box labeled “Use custom color”, and choose your custom color. The status bar which shows the server name will use that color in the future.