SQL Server Management Studio (SSMS) is still the premiere tool when it comes to tooling for the SQL Server database system. The new hotness is certainly Azure Data Studio – the newer, cross-platform, modern tool, but it is not there yet, in terms of the expansive feature-set of Management Studio. SSMS has been around for about 17 years, first released with SQL Server 2005. It was a successor to Enterprise Manager and Query Analyzer, the official tooling that came with SQL Server 2000 and prior versions. Unfortunately, SSMS is Windows-only so if you’re on a Mac or Linux, Azure Data Studio is still your best alternative.
Although I have been using SSMS since the very beginning, I’m still discovering neat, new (to me) features often. I wanted to start compiling a list of these gems. While the complete feature list of SSMS would fill a (very large) book, I’ll start with this humble post, highlighting a few features that I personally find appealing.
Color-Code Your Environments
It’s easy to lose track of the particular instance of SQL Server that your working with. It’s common to have several query windows open at the same time, perhaps connected to the same database but in different environments. May be in one window, you have the QA instance open while in the next window, it’s the production instance. You don’t want to accidentally update a whole bunch of records in the production database when you were really meaning to do that in development!
In SSMS, you can save each database connection as a server registration. In the Add Server Registration and Edit Server Registration dialogs, in the Connection Properties tab, you can select a custom color for that connection. After you’ve done so, whenever you open a query window to that particular instance, the bottom status bar of that query window will bear the same color as its background color. This can serve as a quick visual indicator as to what environment you’re working with. You can come up with your own scheme that you’ll find easy to remember… perhaps, you can color all your production instances red while using greens for your non-prod environments.
Get Object Names by Dragging and Dropping
Did you know that you can grab an object from the object explorer and drag it onto a query window and it will provide you the name of that object in text form? If you drag and drop the Columns folder, it will give you a comma-separated list of those column names as text. You can also do the same with the Keys folder, Statistics, Stored Procedures and so on. Next time you want to send the list of column names for that table to your colleague on Slack, remember this nifty trick.
Split Pane
When working with a query that has gotten extremely lengthy, have you found yourself scrolling up and down like a mad person… perhaps you want to go grab the columns that you specified in your select clause up top, to put in your group by clause, down below. Next time you find yourself in that situation, grab the handle bar icon towards the top-right corner, just above the scrollbars, and drag it down. You’ve got yourself a split-pane! Now you can see and work with two different sections of a lengthy page, all in one view, with minimal scrolling.
Bonus Tip: If you’re entirely new to the world of split-panes, you’re in for a treat. Similar features exist in many other products, from Microsoft and others… Visual Studio, VS Code, Microsoft Word, just to name a few.
Hover Tooltips
Hover over the * in a SELECT * FROM... query. It will display all the column names in that table. You can also hover over a column name in a query and it will display the type definition for that column.
Keyboard Shortcut for “sp_help”
You may already be familiar with the sp_help and sp_helptext system stored procedures. For example sp_help 'my_table' will give you all sorts of meta-data about your table whereas sp_helptext 'my_stored_proc' would return the text definition of your stored procedure. But when you’re in a query window, you don’t have to execute the sp_help stored procedure manually on a given object. Instead, you can simply highlight the object and press Alt + F1 on your keyboard and SSMS will automatically run sp_help on that object for you.
Script Out Your Database (Including Data)
Have you ever wanted to quickly share that proof-of-concept database that you’ve been working on with your teammate but you didn’t want to go through the process of backing up your database and having the recipient restore it? SSMS gives you the option of scripting out your database, including the data contained in each table. It will generate INSERT statements for each row. Keep in mind that this could get out of hand very quickly if your database contains lots of data but for a small PoC database, this may prove sufficient.
You can do this by right-clicking on your database name, and navigating to Tasks > Generate Scripts. In the “Set Scripting Options” view, click on the Advanced button. Here you can change various options for the script output including the option to output both the schema and the data from your database.
Snippets
Do you find yourself googling for the syntax of various constructs in SQL? Try Ctrl + k + x instead. SSMS comes with snippets for a good number of SQL objects that you can dump on to a query window and tab through.
Closing Remarks
I have barely scratched the surface of all the goodness that’s embedded in SQL Server Management Studio. Today, we mostly looked at the Query Editor and Object Explorer. SSMS also contains a lot of capable tools for query optimization, performance tuning analysis and recommendations. It comes with a whole array or reports giving you all sorts of insights into the health of your database. Stay tuned for future posts where we dig into some of those other areas of SSMS. In the meantime, let me know what features you, dear reader, think is cool and worthy of a mention in a future post.