Our tools can make us more efficient and productive – if we take the time to learn their secrets. I spend a lot of time in SQL Server Management Studio (SSMS) and have learned valuable tips and features along the way from many others (links provided).
There are my favorite tweaks to improve your working environment.
- Register all your servers into logical groups and assign different server colors in SSMS
- Query tab names are too long and unreadable, unless you configure SSMS tabs to show only file names
- Don’t box yourself in. Auto-hide Registered Servers, Object Explorer and Properties panes and toggle results pane using Ctrl-R
- Clean up your toolbars. Remove buttons you never use and free up screen real-estate
- Turn on line numbers in the left margin
- Setup keyboard shortcuts, Tools > Options > Keyboard
Shortcuts help us get things done quicker, that’s a good thing. There are tons of shortcuts and you probably won’t learn them all or keep a list by your keyboard. A good rule of thumb is, whenever you find yourself frequently repeating a task and stopping to fiddle with the GUI, you might want to learn an existing keyboard shortcut or create your own. Browsing the menus and toolbars will reveal many shortcut alternatives.
- Toggle results pane with Ctrl-R and free up your coding space between runs
- Execute partial script: Ctrl + Shift + Up/Down then F5 will highlight and execute everything upward or downward
- Also placing a temporary RETURN command in your script acts as an exit statement preventing anything from accidently executing after it (caution if you use GO statements the RETURN will only exit the current GO batch)
- Comment code block: Ctrl-K Ctrl-C, Uncomment code block Ctrl-K Ctrl-U
- Open new query window: Ctrl-N
- Indenting code: highlight lines Tab to increase indent, Shift + Tab to decrease indent
- Vertical block editing: Shift + Alt + Up/Down Arrow Key or Alt + Up/Down Mouse Drag (occasionally very useful, see it to believe it)
- Switching servers is quicker when you use the toolbar button to change connection (or create your own shortcut)
- Plus more shortcuts than you can handle
- Auto-completion and quick peek features are way better than native SSMS and proven huge time savers
- F2 to rename any alias and variable for every instance within your script (also smart rename for refactoring objects)
- Ctrl-F12 on a table, sproc, or view in your script and it will automatically locate and select it in object explorer pane (no more drilling and scrolling and drilling and scrolling)
- Ctrl-K Ctrl-Y Reformat script according to your formatting preferences (highly configurable and powerful)
- Tab History allows you to find every script you’ve written and search them by keyword – even if you never saved them ( SSMS now provides crash protection, but Tab History has saved me time and prevented unnecessary re-work)
- Server tab color coding is better than that provided by SSMS
- Snippet Manager and 12 SQL Prompt Tips need I say more?
The Dark Theme
Many SQL users are still using the default bright white background, but many have joined the dark side. There is much science and debate on light vs dark backgrounds and numerous factors play into the equation. Whatever the science, many developers using a dark theme claim they can sit in front of a monitor for longer periods with more comfort and less eye strain. So it’s worth trying it out and deciding for yourself. When I first saw a dark theme, my reaction was negative, but I decided to try it out for one week – years later, I have never reverted back. Even Yoda knows, “the dark side is quicker, easier and more seductive and forever will it dominate your destiny.”
I first became familiar with the default Visual Studio dark theme which, I think, got the contrast balance more right. I couldn’t understand why this theme wasn’t made available in SSMS as well, so I eventually designed SQL Grinder Dark Theme based on it (see screen shot). Here is my environment complete with Hack font, single toolbar row, color coded query tabs (that only display the file names), and auto-collapsed sidebar windows.
The setting file contains all SSMS settings tweaks along with dark color scheme. You can choose to just import colors or everything, just make an export of your current settings first. From over 150 text editor elements, I only focused on the T-SQL subset, which met my needs. If you see something you don’t like, its easy to change, but finding the correct display item can sometimes be a challenge. This SSMS 2016 setting file should also work with SSMS 2012 and 2014 versions that use XML settings files.
Microsoft has continued to leave the dark mode in SSMS unfinished through many versions even though many people have requested it. However, they were nice enough to put it in anyway and just disable it. You will have to comment out the configuration lines that cause it to remain hidden from you. For SSMS 2016 and SSMS version 17 see How to Enable Dark Theme for SQL Server Management Studio. UPDATE: For SSMS version 18 see bottom of this article.
Even with a dark theme, there are many to choose from and personal preferences is subjective. You can download themes made for Visual Studio which may not work as well for SSMS or make your own the hard way. The problem, IMHO, with most themes I tried is not maintaining a lower contrast. Many use too many bright text colors with high contrast and use pure white instead of light gray. Some use a faded black or bluish backgrounds to compensate, which isn’t necessary if you don’t go crazy with the text colors.
A dark theme will work better when the surrounding environment doesn’t provide a high contrast. Room lighting and screen brightness can also be a factor in your viewing comfort, but can also be subjective. Some people can work on a laptop with maximum brightness sitting in a dark room. That’s high contrast and hurts my eyes. My preferred daytime environment is a natural lit workspace (no lights) or nighttime with maybe some soft background light – always with a low screen brightness.
UPDATE: Windows 10 now offers option to auto enable night mode that warms the colors on your screen in the evening hours.
Even though SSMS has been using the Visual Studio shell since 2012 to support themes, it only applies to the query window and not the rest of UI like it does in Visual Studio. The side bar windows, results pane and rest of the UI don’t support the theme colors giving an inconsistent experience. When using a dark theme, I minimize the white contrast of the rest of the UI by auto-hiding side panes and use Ctrl-R to toggle the results pane.
Your font also plays an important role in screen readability. It is recommended to stick with a fixed width font for coding and there are only a handful to choose from like the default Consolas font. However, I have switched to the Hack font which has been specifically designed for coding environments. It is easy to install and set it as the default font for your text editor in SSMS.
Save Your Settings!
After investing time getting things just the way you like them, export all your settings and save them in your favorite cloud storage, so you can quickly move from one environment to the next and have all your familiar settings back in seconds. When importing you can choose only colors and fonts if you wish to leave the rest of your environment alone. I also export my registered server settings and since I use RedGate SQL Prompt and Multiscript, I export those settings as well and store them together with the Hack font files. This saves me a ton of time when switching to a new computer.
Feel free to leave comments on your favorite settings and color preferences.
UPDATE: SSMS Version 18
The hidden dark theme still remains hidden with this release and Microsoft has also moved things around.
SSMS Version 18’s new path to the ssms.pkgundef file:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE
Now you’ll only have to comment out one single line at the bottom of the file (must be edited as administrator)
// Remove Dark theme