SSMS Productivity Tips

Like any tool that I use, I try to use it to its full potential. Yet, I have found that most people using SSMS don’t know most of the features of the IDE. Because of this, I will review some SSMS features and tips to boost productivity and code faster.

1. Inserting a Comma-Separated List

Every select statement needs a comma separated list of columns you would like to return. But, this can be a pain to type especially for many columns.

The trick is not to write them out to begin with. From Object Explorer, drag the “Columns” item and drop it onto a query window. This generates a list in your query window. Then you only need to add the surrounding query text.

2. Selecting a Block of Code

Now that you have all the columns from your tables, you may need to remove the references to them. This can be a tedious process depending on how many columns you have. The shortcut is to hold ALT+Shift and drag your cursor (or use arrow keys) over the text you want to delete. This allows you to highlight a rectangular block for easy deletion.

[Screenshot: ALT+Shift block selection example]

3. Inserting a Block of Code

There is another benefit to the ALT+Shift shortcut: you can use this method to insert text on many rows in the same area. This makes it easy to create comma delimited lists or prefix values. A great demonstration of this is on Brent Ozar’s website.

4. Adding Line Numbers

Line numbers are handy when debugging code. It is the reason code editors tend to have them on by default. But, this is not the case with Management Studio. To add these select Tools -> Options. In the dialog box that appears click on Text Editor and then Transact-SQL. In the display section on the left, select the check box for Line numbers.

5. Color Coding Connections

Many of us tend to work on many servers at once. This can lead to some confusion when trying to execute queries on a specific server. SSMS has the ability to color code specific connections. This displays at the bottom of the query window.

For example, green for test or red for production.

When connecting, select the Options button in the “Connect to Database Engine” window. Then select the Connection Properties tab. Select the check box towards the bottom of the window and use the “Select…” button to choose a color.

6. Moving Columns Without Changing Code

It may not be obvious but it’s possible to move columns inside the results pane. To do this, start by selecting the column you want to move and then dragging it to your desired location. This relocates that column to the new location much like using Excel. This is a huge benefit to productivity, especially if your query took a long time to run.

7. Splitting the Query Window

When working on long queries it can be difficult to analyze what is happening. In SSMS you are able to split the query window so that it will show your code twice with independent scroll bars.

First select the split icon in the top right hand corner of your query window.

[Screenshot: Split window icon location]

Now drag this icon down to create the second window. Once you have your second window at a reasonable size, it will have its own scroll bar.

8. Finding Error Lines

When writing new queries, you will often encounter errors. These errors are often misleading in long code. The shortest way to find the error is to double click on the error message. This highlights the line that threw the error.