A few years ago, one of the guys I work with was growing increasing frustrated with SQL Server Management Studio (SSMS). His frustrations largely stemmed from the fact that SSMS’s Intellisense/autocomplete functionality sort of sucks. Sometimes it works ok, sometimes it doesn’t trigger at all, and sometimes it triggers…but after VERY long delays. When you’re moving along, writing a SQL script, you don’t want to be bogged down waiting for the editor. So my co-worker went on a quest to find something that could do Intellisense better in SSMS than SSMS does it. That was when he found SQL Complete, by Devart (link).
He installed the trial and fell in love with it. The Intellisense/autocomplete actually worked – no delay. And the autocomplete options were far superior to those found in default SSMS. Once he showed me the JOIN autocomplete, I was sold. I’ve been using it for the past few years and it’s provided a huge improvement in productivity when writing SQL in SSMS. They keep adding features and I’m still discovering new cool stuff on a regular basis.
Below are some of the highlights of my favorite features.
Intellisense / Autocomplete
To me, this rivals the experience I get writing C# in Visual Studio – it’s that good. You start typing a simple SELECT statement, like in the example in this screenshot:
Immediately, the autocomplete pops up giving you the options so far. In this case, I get a couple of table name matches. The selected table pops out with some additional info, showing me the tables schema as well as an estimated row count. I hit Enter to select the table and move on.
Now I want to use a JOIN to grab data from another table. So I start my JOIN statement, autocomplete finds the table I want, and then the magic happens – it automatically finds potential JOIN column matches based on column names and lets you select from them. I don’t have to search for the columns, I don’t have to type it all out, and it qualifies the column names with the appropriate table names. Simply awesome. Just having the JOIN completion might justify its cost to me by itself.
A few other key points related to autocomplete:
- Using autocomplete does wonders for consistency in capitalization. Table names, column names, and even keywords are completed as well, your scripts end up easily looking great, even without the help of a formatter.
- Fully-qualified table names instead of aliases
- Now, instead of using aliases for all table names, I prefer to use the fully-qualified name, which makes it much easier to read and keep track of things, especially in large, complicated scripts. Before that would have been painful due to all of the extra typing, but now with SQL Complete it’s even easier than using aliases the way I used to do it.
Ever need to restart your machine for something like Windows updates and have to go through all of your open SSMS tabs and decide what stays and goes? Now it just automatically resumes where I left off once I fire SSMS back up. Initially when this started happening, I didn’t even realize that it was SQL Compete that was doing it. I assumed it had been added to SSMS. It wasn’t until I was showing SQL Complete to a new employee at our company and we were reviewing its options that it dawned on me. It even has a session history.
Another feature I didn’t realize existed until fairly recently. Very handy for the times that you didn’t save a script, then regretted it shortly thereafter.
Find in Results
This is a fairly recent addition to the feature list. Just right-click in the results grid and start typing what you want to find. It finds and highlights it in any column in the results.
Ever kick off a query that’s going to take a while, then have to keep checking back on it to see if it’s done yet? With notifications enabled, you’ll get a pop-up in the bottom-right corner of your screen when execution completes. Plus there are configuration options to make it suit your needs.
A few other things worth mentioning:
- Export/import profiles
- Come up with a standard for your team then share the profile
- Lots of pre-defined snippets, plus create your own, to cut down on typing time
- An option to format the current document (for times when your co-worker writes some messy-looking SQL and sends it to you and your eyes start to bleed). Also an option to format a specific file, or files in a directory.
- Connection-Based Tab Colors
- For the times when you don’t actually want to run that script on production. Set a color for production vs. dev, etc.
- LOTS of features and customization options