SQL 101 (6 Part Series)
I spend a lot of my day in 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 start up
- 7 - Adding commas to a list in one step
- 8 - Select a colour for each environment you use
- 9 - Use the performance reports
- 10 - Filter objects in Object Explorer
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 in Object Explorer and dropping it onto to a query window.
By clicking on the query header and selecting 'new vertical group' you are able to then edit two queries side by side. Both queries can then be scrolled and edited independently.
By using the shortcuts instead of pointing and clicking you can work faster and spend more time writing queries.
These are the shortcuts I find handy:
- F5 - Execute currently selected code
- CTRL + R - Show/hide the results pane
- CTRL + N - Open a new query window
- CTRL + L - Display the query execution plan
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:
If you have lots of columns in your result set and want to change their order in the grid view without having to rerun the query. Simply drag the column headers and drop them in your preferred order.
When this option is turned on SSMS opens up a new Query window once you are connected. To turn this on go to:
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.
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:
Instead of trawling through scripts you can use the standard SSMS performance and troubleshooting reports. The information is presented in graphs and tables so you don’t have to do anything else to make them user-friendly. To access the reports, right click on the database you’re interested in:
If you’re searching for an object with a specific name you can filter objects to make finding it easier. Expand the database you’re interested in:
From here you can filter by name.
To clear the filter, right-click Tables, and then select Remove Filter.
This post first appeared on helenanderson.co.nz
Photo by Adrianna Calvo from Pexels