I put a lot of thought into doing the least amount of work possible, and you should too. That’s not to say I’m lazy–quite the opposite–it’s to say we all need to put some time into working smarter. Working smarter is one of the reasons I’m such a fanboy of RedGate’s products, both for my .NET as well as my SQL Server and MySQL work. One of my favorite tools is SQL Prompt, an SSMS plugin that adds “missing” functionality, or has similar features which work better. Either way, if you use SSMS, SQL Prompt will make your life better. Here are 10 reasons how:
1. Better snippets. How many times do you start with “Select * From” or “Select Top 100 * From”? How often do you love typing all of that, each time, over and over and over again? Work smart with SQL Prompt, and type “ssf” or “st100” followed by any key, and have those commands auto-expanded for you. You can even easily make your own snippets, or grab one from the community repository at https://github.com/gvohra/sqlpromptsnippets.
It’s true that since 2012, SSMS has also had snippets, but working with them is clunky. Here’s how to insert a snippet, and here’s how to create one. Ugh.
Pro tip: Try the “yell” snippet.
2. More intelligent IntelliSense. As with snippets, SQL Prompt has a better implementation of an existing SSMS feature. IntelliSense is Microsoft’s trademark for the “what we think you mean to type next” feature, and in .NET it’s a great implementation. In SSMS, the implementation isn’t as smooth.
SSMS’ Intellisense orders everything alphabetically–user tables and views are mixed in with system objects. This will get annoying fast if you’re a down-arrow user, or if your tables start with the same letters as system objects.
In contrast, SQL Prompt groups objects by type, then alphabetically. All user tables are listed first, arranged alphabetically. Then user views, system objects, and so on. The suggestions are filtered in the same way as you type the object name, making it very quick to select what you need with only a few keystrokes.
SQL Prompt is also alias-aware, and will make suggestions for temporary tables and procedure variables, including table variables.
SQL Prompt gives you more options for its behavior that SSMS’ Intellisense, also.
3. Query reformatting. When writing .NET code, Ctrl+K, Ctrl+D “prettifies” the code–fixing indents, line breaks, and so on to improve readability. The same key combination in SSMS is reserved, but doesn’t reformat a query (apparently it’s a feature in the “text editor” only).
SQL Prompt makes it happen in the query editor with Ctrl+K, Ctrl+Y. There are all kinds of options you can turn on, but one of my favorites is “Expand Wildcards” (off by default, enable at SQL Prompt >> Options >> Format/Styles/Actions). Undo (Ctrl+Z) reverses the formatting, like it should.
4. Copy as IN clause. Something else we do when debugging–copy a set of values, reformat it somehow to make a list, then paste it in another query. We all have our ways of reformatting, from query magic to macros in text editors. None of that is needed anymore–SQL Prompt adds a menu extension for “Copy as IN clause”, where a selected column of values can be copied and pasted into a query.
5. Open in Excel. Excel is the world’s #1 BI tool, and is commonly used for data debugging and profiling. Rather than “Copy with Headers” on your resultset, you can now just open he results directly in Excel.
6. Script as INSERT. When testing or debugging, I’ll often take the results of a query and reformat them so I can insert them into an temp table, then use that temp table as part of the next validation step, and so on. This feature greatly simplifies my process by creating an INSERT statement with a temp table and all the selected values in a new tab as soon as you choose this option. This is a very intelligent feature–you can select a single value, multiple values, or the entire resultset and the ensuing INSERT statement will be accurate.
7. Colored tabs per connection. SSMS lets you color the status bar for each connection (server and database), which means you know which tab you need just by the color (unless you’re the kind of person who has eight bazillion tabs open at once, not much anyone can do there). For me, I color code by server, and local=blue, test=green, beta=yellow, and prod=red. Tab colors FTW!
8. Execution warnings. I’m sure you’ve never forgotten a WHERE clause in an UPDATE or DELETE statement, just the same as I never have. Yeah, I’ve never done that… Because I’ve never done that, I actually write these statements backwards, starting with the WHERE clause, just to make sure I put one in.
Here’s the feature which may save your butt, big-time. Would have been great to have when I executed a few queries of notoriety.
9. Matching object highlighting. Want to see everywhere an identifier (column name, alias, etc) is used? Select any instance, and all the other instances of that identifier are highlighted. Click on whitespace to unhighlight.
(I stole this picture from the release notes at http://www.red-gate.com/blog/building/sql-prompt-7-2-released-sql-server-management-studio-2016-support-new-features).
10. Development as a stand-alone product. SSMS features are added with new versions of SSMS, every 2-ish years. SQL Prompt is a stand-alone product and adds dozens of useful features during those intervals. You can see teh SQL prompt release notes at http://documentation.red-gate.com/display/SP7/Release+notes+and+other+versions to get an idea of what a “point release” can include. Beta features are public months in advance–see http://www.red-gate.com/blog/redgate-products/why-support-and-upgrades-matter for an example of how much is going on at all times.
In conclusion: work smart, my friends. Some of these features are in the SSMS base product, but SQL Prompt does a better job, and some of these features are unique to SQL Prompt. If SQL Prompt can do this (and more) for just writing queries, imagine what the whole SQL Toolbelt can do for your entire database development process–writing, source control, testing and deployment. There is no reason to do any of that half-assed.