Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

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.

recreation

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.

addremove

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).

linenum

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.

color

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.

color2

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.

sound

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.

len

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.

save

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.

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

5 Comments on "My SQL Server Management Studio Setup"

  1. Ernest Libertucci | December 18, 2017 at 1:28 pm | Reply

    Love this post! The only “quibble” I would say is .. I would suggest never using the table designer, but I am in the camp of making things scriptable (Very typical to have to move change X to environments Y, Z .. N). That being said, I would still agree with your suggestion.

  2. Thank you! I’m already using some of then.

  3. There is one big soh having the status in progress and another (GD type) non-validated entry.

  4. Hi Tim,

    Thank you for the post. I have implemented the custom colors and they are not showing up when I connect and run queries off of the applicable databases. I think it is because I am using the version of SSMS 17.6 utilizing the hidden ‘Dark’ color theme, which I have the ability to use as I have found a way to make it present in the selection screen.

  5. After further review, the custom colors are working, what solved it was a restart of the application. ‘Have you tried turning it off and on again?’

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: