I spend a lot of my day in SQL Server Management Studio (SSMS). It\u2019s highly customisable with plenty of tools and shortcuts to make life easier.<\/p>\n\n\n\n
These are just a handful of the options available that I find most useful.<\/p>\n\n\n\n
1 – Create a list of comma-separated columns<\/a>
2 – Use the query window to edit side by side<\/a>
3 – Use the keyboard shortcuts<\/a>
4 – Displaying line numbers<\/a>
5 – Moving columns in the results pane<\/a>
6 – Open a new query window on startup<\/a>
7 – Adding commas to a list in one step<\/a>
8 – Select a colour for each environment<\/a>
9 – Use the performance reports<\/a>
10 – Filter objects in Object Explorer<\/a><\/p><\/blockquote>\n\n\n\n
\n\n\n\nCreate a list of comma-separated columns<\/h2>\n\n\n\n
If you want a comma-separated list of all columns from a table in your query window, you can do this by dragging the \u2018Columns\u2019 item from Object Explorer it onto to a query window. No need to type each column name out one by one.<\/p>\n\n\n\n
\n\n\n\nUse the query window to edit side by side<\/h2>\n\n\n\n
When editing two code blocks it can be useful to see them both side by side.<\/p>\n\n\n\n
By clicking on the query header and selecting \u2018new vertical group\u2019 you are able to then edit and scroll two queries independently.<\/p>\n\n\n\n
<\/figure>\n\n\n\n
\n\n\n\nUse the keyboard shortcuts<\/h2>\n\n\n\n
Spend more time writing queries by using the shortcuts instead of pointing and clicking.<\/p>\n\n\n\n
These are the shortcuts I find handy:<\/p>\n\n\n\n
- Execute currently selected code – F5<\/strong><\/li>
- Show\/hide the results pane – CTRL + R<\/strong><\/li>
- Open a new query window – CTRL + N<\/strong><\/li>
- Display the query execution plan – CTRL + L<\/strong><\/li><\/ul>\n\n\n\n
\n\n\n\nDisplaying line numbers<\/h2>\n\n\n\n
Enabling line numbers makes it easier to find where the problems are when SSMS throws you an error.<\/p>\n\n\n\n
This is especially useful when you\u2019re working with long queries. To turn this on go to:<\/p>\n\n\n\n
Tools > Options > Text Editor<\/strong><\/p><\/blockquote>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\nMoving columns in the results pane<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
\n\n\n\nOpen a new query window on startup<\/h2>\n\n\n\n
When this option is turned on, SSMS opens up a new Query Window once you are connected. To turn this on go to:<\/p>\n\n\n\n
Tools > Options > Environment > Startup<\/strong><\/p><\/blockquote>\n\n\n\n
<\/figure>\n\n\n\n
\n\n\n\nAdding commas to a list in one step<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Hold the key down as you click where you\u2019d like to start and you should see a faint line appear.<\/p>\n\n\n\n
Drag this down to where you\u2019d like to start editing and make your changes. This can also be used for deleting in one step.<\/p>\n\n\n\n
\n\n\n\nSelect a colour for each environment you use<\/h2>\n\n\n\n
Options > Connection Properties > Use custom colour<\/strong><\/p><\/blockquote>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\nUse the performance reports<\/h2>\n\n\n\n
The standard performance and troubleshooting reports are presented with user-friendly graphs and tables so you don\u2019t have to do anything else or trawl through scripts finding what you need.<\/p>\n\n\n\n
Right click on the database you\u2019re interested in:<\/p>\n\n\n\n
Reports > Standard Reports > Pick your report<\/strong><\/p><\/blockquote>\n\n\n\n
\n\n\n\nFilter objects in Object Explorer<\/h2>\n\n\n\n
Use the objects filter to make finding an object by name easier. Expand the database you\u2019re interested in:<\/p>\n\n\n\n
Tables > Right click > Filter > Filter Settings<\/strong><\/p><\/blockquote>\n\n\n\n
From here you can filter by name. To clear the filter, right-click Tables, and then select Remove Filter.<\/p>\n\n\n\n