Whether you’re GPSing around traffic, going through a drive-thru, or using ALT+TAB to switch between open windows, life demands shortcuts. You know the adage – work smarter, not harder. Working with SQL Server is no different - if you’re a busy developer or DBA, you likely spend part of your day writing TSQL code in Management Studio. Fortunately, Microsoft has built in features we can use to make our jobs a bit easier. Here are a few recommended shortcuts and other tips to help save you time and keystrokes.
How often, when you’re browsing data in tables, do you type SELECT * FROM <SomeTable>? (We recommend using the TOP keyword to limit this type of search.) Management Studio allows you to assign query shortcuts to specific keystrokes, much like a macro in Excel. This is great for things you do (or type) frequently, like quickly selecting a subset of table data.
To assign a query shortcut, select Tools and Options from the Management Studio menu. On the left side, expand Keyboard and select Query Shortcuts.
You’ll notice there are a few pre-assigned shortcuts for common system procedures which come in handy. Let’s add one to select the first 1,000 rows from a table. Select the empty box next to Ctrl+3 and type “select top 1000 * from” (without the quotes) then click OK.
Now whenever you’re working on code, you can highlight any table name and use your assigned key combination to display the first 1000 rows from that table. In the example below, we’re using the fully qualified table name, since we keep our stored procedures in our own database on the DR server (not in the live or test DR databases) which we strongly recommend.
Another technique we use commonly is known as a block select, vertical select or alt select. This lets you select ranges of text in a query window and then apply some action to each line – say for example, insert new text, delete text, etc.
Let’s look at an example: say you need order data from OM, but aren’t sure where to start. You find OmOrd_Main, OmOrd_Main2 and OmOrd_Main3, but what’s in them? Use ALT-SELECT to write a single SELECT TOP 1000 * FROM statement for all three.
- Put your cursor to the left of the testfdb.dbo.OmOrd_Main (after you've typed all 3 tables names, each on their own line).
- Press and hold the ALT key, then drag your cursor down to the left of testfdb.dbo.OmOrd_Main3 (you should see a vertical line).
- Type “SELECT TOP 1000 * FROM “ and it’s inserted before each table name.Now you can quickly browse each table without typing (or copying and pasting) repeatedly. This editor trick is very useful, if you start thinking about how often you need to do repetitive typing, say like adding a comma to a list of values so you can use them with an IN statement. Watch this 30 second video from our YouTube channel to see it in action.
You can see that Management Studio has a few tricks up its sleeve. In addition to the query shortcuts and the ALT-SELECT tips, here’s a short list of keyboard shortcuts you can use - some you may know and others may be new to you. We’ve also included a handy cheat-sheet at the bottom you can print, cut and paste on your monitor.
F5 or CTRL + E
Execute the highlighted text, or if nothing is selected, execute all code in the open query window.
CTRL + R
Show/Hide the Results Pane. Quickly check the results of your query and hide the pane when you want to dedicate more screen space to your code.
CTRL + N
Open a new query window.
TAB or SHIFT + TAB
Increase or decrease indent for a row or a group of selected rows. Very useful to help your code be more readable and therefore easier to understand and modify.
SHIFT + ALT + ENTER
Toggle full screen. This hides all the user interface elements and allows your query code to take up the entire screen. Useful to eliminate unnecessary clutter while working, or for presentations.
CTRL + SHIFT + U or CTRL + SHIFT + L
Make the highlighted text upper or lower case. This can be used to format certain elements to be more easily readable, but also can be combined with ALT-SELECT to easily convert a block of code or a list of items into uppercase or lowercase.
CTRL + F
Opens the Find dialog, used to search for text.
CTRL + H
Opens the Find and Replace dialog, used when you need to search and replace specific text values. Very useful when copying the query to a different database that requires replacing database references.
CTRL + SPACE
Force Intellisense lookup. Intellisense is an optional feature that provides a lookup into existing db objects as you type. It also underlines elements in red if they don’t appear to exist; this shortcut forces that lookup to occur on demand.
CTRL + SHIFT + R
Refresh local cache for Intellisense. Sometimes Intellisense doesn’t recognize newly-created objects; this shortcut forces SSMS to refresh its object list in cache.
CTRL + G
Go to a specific line number. Very useful when navigating large stored procedures with hundreds or thousands of lines of code.
Here's a handy list you can use as a reference. Just print, clip and save! (Yes, some people still like to print things.)