loading...
Cover image for How to customise SQL Server Management Studio (SSMS)

How to customise SQL Server Management Studio (SSMS)

helenanders26 profile image Helen Anderson Updated on ・4 min read

Data Analysis (14 Part Series)

1) Speed up your queries with indexes 2) Resources for beginner data analysts 3 ... 12 3) SQL concepts from A to Z 4) 6 ways to search with System Tables 5) Pearl Jam, Nirvana and Beautiful Data Visualisations 6) Entity Relationship Diagrams explained by Sonic the Hedgehog 7) How to use the VLOOKUP Excel function 8) SQL Joins without the Venn diagrams 9) SQL aggregate functions and Mariah Carey 10) Why I use SQL CTEs 11) How to customise SQL Server Management Studio (SSMS) 12) Have you VACUUMed your tables lately? 13) Why you need SQL window functions (part 1) 14) Why you need SQL window functions (part 2)

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 in Object Explorer


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 it onto to 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.


Alt Text


Use the keyboard shortcuts

Spend more time writing queries by using the shortcuts instead of pointing and clicking.

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


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


Alt Text


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


Alt Text


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.


Alt Text


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


Alt Text


Use the performance reports

The standard performance and troubleshooting reports are presented with user-friendly graphs and tables so you don’t have to do anything else or trawl through scripts finding what you need.

Right-click on the database you’re interested in:

Reports > Standard Reports > Pick your report


Filter objects in Object Explorer

Use the objects filter to make finding an object by name easier. Expand the database you’re interested in:

Tables > Right-click > Filter > Filter Settings

From here you can filter by name. To clear the filter, right-click Tables, and then select Remove Filter.


Alt Text


There are many ways to customise SSMS but these are the most useful ways I’ve found to make it work for me. Which ones have I missed that make your life easier?


Read more


This post originally appeared on helenanderson.co.nz

Data Analysis (14 Part Series)

1) Speed up your queries with indexes 2) Resources for beginner data analysts 3 ... 12 3) SQL concepts from A to Z 4) 6 ways to search with System Tables 5) Pearl Jam, Nirvana and Beautiful Data Visualisations 6) Entity Relationship Diagrams explained by Sonic the Hedgehog 7) How to use the VLOOKUP Excel function 8) SQL Joins without the Venn diagrams 9) SQL aggregate functions and Mariah Carey 10) Why I use SQL CTEs 11) How to customise SQL Server Management Studio (SSMS) 12) Have you VACUUMed your tables lately? 13) Why you need SQL window functions (part 1) 14) Why you need SQL window functions (part 2)

Posted on by:

helenanders26 profile

Helen Anderson

@helenanders26

Making applications go faster at Raygun, AWS Data Hero, and tag moderator on Dev.to. Database concept you don’t understand? Let me know, I’ll write a post!

Discussion

markdown guide
 

If you have the budget there are also a number of great extensions available from Redgate and pragmatic works including formatting, linting, source code compare.and check-in, advanced intellisense and script snippets

 

I didn't know about some of these customizations, so thanks!

How do you feel about SSMS compared with Azure Data Studio?

I spend much more of my time on the application side (rather than the data side), so there are things I don't use that a DBA or something working with data a lot would find lacking in Azure Data Studio.

I've mostly been using Azure Data Studio because it's cross platform and works similarly to VS Code, which I spend a lot of time in.

ADS also sometimes feels a little snappier? I'm hoping the extensions ecosystem for ADS grow like it has for VS Code - working with extensions in SSMS and Visual Studio is sub-par ☹.

 

Nice! Glad you found something useful in there.

I haven't used anything Azure as my workplace is all in on AWS. We use a small SQL Server instance to run stored procedures as when we rolled out Redshift this wasn't supported so here we are :D

Sounds like your experience with ADS would make for a good blog post.

 

Great article, have been working with ssms for so many years and still don't know all his possibilities.

Thx :D

 

You're welcome!

I'm sure there are a lot more I have missed. I've been learning more while in lockdown and pairing with more people on my team. Things they have used for years are completely new to me and vice versa.

 

Also be thankful for the amount of effort put into SSMS and VS code by Redmond. The closest equivalent for Oracle Devs is TOAD which is £1000 a seat and nowhere near as functionally rich.