I spend a lot of my day in SQL Server Management Studio (SSMS). It’s highly customisable with plenty of tools and shortcuts to make life easier. These are just a handful of the options available that I find most useful.
1 – Create a list of comma-separated columns
2 – Use the query window to edit side by side
3 – Use the keyboard shortcuts
4 – Displaying line numbers
5 – Moving columns in the results pane
6 – Open a new query window on startup
7 – Adding commas to a list in one step
8 – Select a colour for each environment
9 – Use the performance reports
10 – Filter objects
Create a list of comma-separated columns
If you want a comma-separated list of all columns from a table in your query window, you can do this by dragging the ‘columns’ item from object explorer onto a query window. No need to type each column name out one by one.
Use the query window to edit side by side
When editing two code blocks it can be useful to see them both side by side. By clicking on the query header and selecting ‘new vertical group’ you are able to then edit and scroll two queries independently.
Use the keyboard shortcuts
These are the shortcuts I find handy so I spend more time writing queries instead of pointing and clicking.
- Execute currently selected code – F5
- Show/hide the results pane – CTRL + R
- Open a new query window – CTRL + N
- Display the query execution plan – CTRL + L
Displaying line numbers
Enabling line numbers makes it easier to find where the problems are when SSMS throws you an error. This is especially useful when you’re working with long queries. To turn this on go to: Tools > Options > Text Editor
Moving columns in the results pane
I often find I have a result set with a lot of columns and want to change their order without having to rerun the query. Simply drag the column header and drop it in your preferred order.
Open a new query window on startup
When this option is turned on, SSMS opens up a new query window once you are connected. To turn this on go to Tools > Options > Environment > Startup.
Adding commas to a list in one step
Instead of manually adding commas or quotes between a list of columns or strings you can do this all in one go by using Alt.
Hold the key down as you click where you’d like to start and you should see a faint line appear. Drag this down to where you’d like to start editing and make your changes. This can also be used for deleting in one step.
Select a colour for each environment you use
If you are in multiple environments each day, you may find it useful to have a different colour for each connection – pink for UAT, blue for Dev, red for Prod indicating where you should be cautious. On startup go to Options > Connection Properties > Use custom colour.
Use the performance reports
The standard performance and troubleshooting reports are presented with user-friendly graphs and tables so you don’t have to trawl through scripts finding what you need.
Right-click on the database you’re interested in and go to Reports > Standard Reports > Pick your report.
Use the objects filter to make finding an object by name easier. Expand the database you’re interested in using by clicking Tables > Filter > Filter Settings to filter by name.
To clear the filter, right-click tables, and then select remove the filter.
There are many ways to customise SSMS but these are the most useful ways I’ve found to make it work for me.