3 Tips for Using SQL Server Management Studio

 

You’ve been using SQL Server Management Studio for a long while now, but you mostly only using the New Query window to write T-SQL and the Object Explorer to do management tasks for an instance. You know it can do more, but you are a busy data professional. You don’t have a lot of time to poke around in the options and you’re not sure where to look or what else it can do to make your life easier. In this post I will cover three SQL Server Management Studio options that will save you time and make you more efficient.

 

How to Display Line Numbers in SSMS

The first tip discussed will be using line numbers. Line numbers can be added to the query editor window. If you’ve ever had to scroll through a long stored procedure and wanted a nice way to remember where you saw something you wanted to change, line numbers is a good way to help yourself with that. To enable line numbers do the following:

  • Click the Tools menu at the top of SSMS.
  • In the drop down then select Options.
  • Then under Text Editor left click Transact SQL.  Now several options will be available related to what happens inside the text editor screen.
  • Left click the box next to Line Numbers to enable the display of line numbers next to your code in the editor.
Line Numbers Option in SSMS

Line Numbers Option in SSMS

 

How to Enable Query Shortcuts in SSMS

The second tip is query shortcuts. Ever find yourself retyping commands like sp_who2, sp_whoisactive and it’s many parameters, sp_helpindex from Kimberly Tripp or other handy procedures?  You can actually execute these by assigning them to a keyboard shortcut rather than having to type them each time you want to run the procedure. having a shortcut assigned to something like sp_whoisactive can really help when you need to jump quickly into troubleshooting mode.

To do this, follow these steps:

  • Go back to the Tools and Options menu.
  • Choose Environment, then Keyboard, then Query Shortcuts.
  • The left column will be labeled Shortcuts.  To the right of the shortcut you wish to use simply left click and type the command you want to assign.
  • Click OK to assign that shortcut.
  • Presto!  Now the next time you want to run that favorite metadata proc, just use the assigned shortcut.

SSMS Query Shortcuts

 

How To Set the Number of Recent Files and Projects in SSMS

The third tip is one of my favorite options to adjust. It is the number of recent files and projects that SSMS displays.  This list gives you access to recently opened files and projects.  You might find yourself working on a large number of projects and scripts at once and this is a handy why to have quick access to all that work. The default is 10 but I don’t always find that 10 is enough for me.

Go to Tools, then Options and Environment.  In the Recent Files section, the number can be adjusted up or down to suit your preference.

 

SSMS Recent Files Option

I’ve given you 3 easy ways to save yourself time and help you jump into action a little quicker when you need to troubleshoot something.

 

Next Steps to Take

  1. Open SSMS and try out these changes to your setup.
  2. While you’re there making changes, click on the View menu and select Template Explorer and play around with the code snippets available in there. Those can be time savers too!
  3. Check out this video from Taiob Ali talking about SSMS options and features that help with performance tuning.
  4. Leave a comment or contact me on Twitter to ask me any questions about this post or anything else related to SQL Server administration.

 

 

 

>