My SQL Server Management Studio Setup

We’re all creatures of habit, and work more efficiently when we know exactly where to find the tools we need. The same holds true with digital tools, and those of us who work with SQL Server will spend a lot of time using SQL Server Management Studio. SSMS has a lot of features, but each data professional will likely use this environment very differently based on their job role and preferences. Fortunately, the SSMS environment is very customizable and allows personal configuration to suit each user.

In this brief post, I’ll share some of the preferences I set in Management Studio, and will show those settings can be saved and shared.

My SSMS Setup

Disable Prevent saving changes that require table re-creation. This one is a no-brainer, and frankly shouldn’t be necessary – the setting I use should actually be the default (but sadly isn’t). By default, SSMS blocks any change from the object designer that would require a table to be dropped and recreated. Since this is a common part of what a data professional will do during development or testing, this is one of the most common settings one should change in SSMS.

To enable operations requiring table recreation, open the Settings window and go to the Designers tab. Uncheck the box as indicated below.

Remove unused buttons from the toolbar. One of the most visible changes one can make to SSMS is to customize the buttons that appear on the toolbar. Management Studio comes loaded with several rows full of tool buttons, but it’s unlikely that every data professional will use all of these. The SSMS UI allow each user to select which of the toolbars will appear, as well as customizing which buttons appear on those toolbars that are visible.

As shown, each toolbar can be customized using the down arrow to the right and selecting Add or Remove Buttons. Or, remove that toolbar entirely by right-clicking the toolbar area and deselecting that toolbar.

Personally, I’m more of a keyboard shortcut guy than a toolbar guy, so I’m a minimalist when it comes to my SSMS toolbar.

Turn on line numbers. Showing line numbers in code is mostly decorative, but can be helpful during troubleshooting or shared development exercises. When executing T-SQL code, some errors will report the line number in which the error occurred. Also, when working with others during development, it is very handy to be able to point your colleague to a specific line number. For those reasons, I always turn on the setting to show line numbers in SSMS code.

Line numbering can be turned on in SSMS by navigating to the Settings window, and drilling into Text Editor –> All Languages (or just Transact-SQL to limit just to T-SQL code).

Again, turning this on doesn’t change the way the code executes, but it does make it easier to identify where an error exists.

Set custom status bar colors for common database connections. When querying multiple environments (Dev, Test, Prod, etc.) using similar code, it’s very important to know which environment you’re working in. A feature in SSMS will allow the assignment of custom status bar colors to known SQL Server connections. What this means is that you can set up one color for Dev, another for Test, and yet another for Prod, and queries connected to those SQL Server instances will show the assigned color in the status bar at the bottom of the query.

Setting up the custom color is done when making the connection, and will be remembered by SSMS for future connections to that same instance.

Once that custom color has been defined, queries connected to that SQL Server instance will show the designated color in queries using that SQL Server connection.

Turn on the Windows beep for query execution completion. This rarely-used option gives you an audio alert when a query completes. Have you ever been multitasking and have had to keep checking a query window to see if the query had completed? SSMS can be configured to sound the Windows default beep notification at the end of each query. This setting is found under the Query Results setting.

Enabling this option will sound an alert when each query finishes executing.

Increase the maximum column output size for text results. Infrequently, I have the need to write query results to text and copy/paste those results elsewhere. By default, when writing results to the output window in plain text (as opposed to a data grid), any text field will be truncated to a maximum of 256 characters. I always set this to the maximum of 8192 characters as shown below.

Save settings to a shared file. If the prospect of repetitively doing all of the above (and perhaps more, depending on your preferences) isn’t appealing, here’s the best part: Most of your SSMS settings are portable from one machine to the next. If you use multiple machines or need to copy your settings to a new client machine, you can easily share custom settings through just a single config file.

As shown below in the Environment –> Import and Export Settings tab, the Management Studio settings are stored in a file in the user’s Windows profile directory.

This file location is configurable, so I just point mine to my Dropbox folder. That allows me to share this among all of the machines on which I use SSMS.

Conclusion

There are numerous configuration options you can set to suit the way you interact with Management Studio. These few are just a handful of settings that I use. Don’t forget to make use of the settings file if you use multiple machines or need to migrate from one SSMS install to another.