I wanted to take a look at three SQL Server Management Studio (SSMS) tips I have come across over the years that I found interesting and helpful. They have been a big help to me and will hopefully make your usage of SSMS more efficient.
Tip #1 – Colors for Connections
Tip #1 is something extra to help protect you from accidentally running a script or making a change in production instead of a test environment. SSMS has the ability to configure a color at the bottom of a query window indicating what server is currently connected. If you configure any test server connections as green then you can relax a bit when running scripts and see a green highlight across the bottom of the window. If you have your setup configured where production servers show red, you’ll have an extra visual indicator to make you think twice before making a change that can affect production. Let’s setup an example.
Note: Ignore some of the strangeness with the server names as I’m doing some testing with SQL Server Docker containers.
When logging into a SQL server, select the Options button at the bottom right.
Under the Connection Properties tab, look down at the bottom where there is a Use custom color checkbox.
If you click the Select button and choose a color, the chosen color will be associated with the SQL server when logging in and opening a query window. Let’s choose green for this test server.
We will now see a green highlight across the bottom when logging in and opening a new window.
If we have red set for production, we will see a red highlight.
Tip #2 – Line Numbers
Tip #2 is something I have sometimes not even noticed until troubleshooting a long script or stored procedure but something that is worth the few seconds it takes to turn on. I’m talking about line numbers. To me, this seems like an option that should be on by default but maybe I’m in the minority.
To turn on line numbers, open SSMS and go to Tools > Options. From there, the selection needed is under Text Editor > All Languages > General.
Tick the Line numbers check box, click the Ok button, and right away you will see line numbers in any open query windows.
Tip #3 – Vertical Select Mode
Finally, Tip #3 is a time saver for when you need to make the same changes or additions to multiple lines. I come across this most when needing to search for a list of ID’s that are not in a SQL query friendly format.
Let’s say someone comes to us with a list of Account_ID values that need to be searched. If we’re going to search the Account table, we need to wrap single quotes and add a comma between each value. This isn’t too big of a deal if there are only a few ID’s that need searched because we can quickly add those characters in manually and run a query. If there are dozens or hundreds of values to add, that isn’t something that be done as quickly. This is where Tip #3, Vertical Select Mode, comes in.
We can start with this in SSMS:
With the cursor at A1875, we can press and hold Alt+Shift and either press the down arrow on the keyboard or click to the last line to create a vertical line along the text:
Once the vertical line is visible, any typed character will be added to each selected line. We can add a single quote to the beginning of each line:
And do the same process at the end of the ID’s to add a closing single quote and comma:
After a little cleanup on the last line to remove the last comma and add a closing parenthesis we can run our query:
This can be used to remove characters as well. Instead of typing new characters, you can press the Delete key to remove characters appearing after the vertical line.
I hope you find these as useful as I do and they can save you some time.
Thanks for reading!
Chad Callihan 