SQL Server Management Studio Tips

Introduction

SQL Server Management Studio (or SSMS) has a lot of depth to it that most of us are probably not even aware of. Typically we get into a habit of doing things on the basis of what we know. Also, I believe that as a DBA my primary concern is the databases and making sure my scripts are correct, and I’m sure I’m not alone here.

Lately I’ve tried to push myself in getting to grips with the IDE and making better use of the built in functionality. In a seriously lose tie in with Friday 13th,  here’s 13 tips for using SQL Studio Management Studio. Some of them are very basic, and some of them are quite obscure.

1. Pressing Ctrl + R when you have the results window open in SSMS will auto-hide it.

BeforeCtrlPlusR

AfterCtrlPlusR

To bring it back press Ctrl + R again.

2. If you accidentally hit CTRL + SHIFT + R then this may be a good thing: This will clear the cache of the Intellisense.

You can do this through the UI by going to Edit  > Intellisense  > Refresh Local Cache.

IntellsneseRefreshUi3. If you’re still having issues with Intellisense ensure that it is enabled by checking the UI. If it is highlighted then it is enabled for this particular query window.

CheckIntellisenseEnabled4. Another place to check for Intellisense settings for Management Studio is under Tools > Options > Text Editor > Transact-SQL > IntelliSense:

IntellisenseSettingsHere you can also set the settings like the file size and underlining errors.

5. If you want to manually prompt for Intellisense then press ALT+RIGHT ARROW or CTRL+SPACE.

PromptIntellisense

6. Intellisense will not work in SQLCMD mode. Check that it is enabled under Query > If you cannot see Query as a menu option click on a Query window

SQLCMD

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Though it is cmd line it can be run through the UI. If that sounds crazy then sure maybe, but it’s a lot easier than running large scripts through the UI than it is the cmd line.

7. You can enable running all new query windows in SQLCMD by going to Tools > Options > Query Execution and selecting  “by default, open new queries in SQLCMD mode”.

2013-09-04 16_00_19-MDXQuery1.mdx - (local)_refresh.Analysis Services Tutorial (CORP_richard

8. If you have a block of T-SQL you want to comment out, highlight it then click CTRL K, CTRL C.

Commented
If you want to uncomment it, then press CTRL K, CTRL U.

UncommentedYou can also do this through the UI using the options on the SQL Editor Toolbar:

CommentUnCommentUI9. If you cannot see the SQL Editor Toolbar then you can enable it by going to View > Toolbars > SQL Editor.

SqlEditorToolbar10. If you need to add some characters to the beginning or the end of a long list in SSMS, you can do so by moving the cursor to where you want to start, then press and hold down a mouse button click, then press and hold down alt, then drag the cursor down the line until you reach the end. You will see a faint grey line. Then you can press whatever character you need to enter and it will appear at the beginning of the line.

clickAltHoldThis is great for entering comma separated values for an ‘IN’ statement.

clickAltHold211. You can check that the connection to your server is active by running a really straightforward query:

 SELECT 1; 

At first you may see an error message like below.

transport-level-errorIf you execute the query again it will reconnect to the server you were connected to.

12. to enable or disable the line numbers in SSMS, click Tools > Options, expand the Text Editor option, expand All Languages and select General

LineNumbersOPtionsNow when you open a new query window the line numbers are displayed.

13. If you need to go to a particular line number press CTRL + G to open the Go To Line window; enter the line number and click OK:

GoToLine


Do you have any tips and tricks with SSMS? It’d be great for you to share them here.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s