SQL Server Management Studio: 10 Tips and Tricks

There’s no doubt that SQL Server Management Studio (SSMS) is the SQL Server DBA’s best friend and most frequently used tool. However, while everyone knows the basics of how to use SSMS to navigate through database objects as well as write and run queries there’s a lot of other functionality that’s built into SSMS that you might have missed. In this article, I’ll show you ten tips that you can use to get more out of SSMS.

1. Speed up your server connections with Registered Servers — If you have a lot of servers re-entering the connection information in Object Explorer every time you start SSMS can be time consuming. SSMS Registered Servers enable these details to be entered just once and reused every time you open up SSMS. To add a registered server open the Registered Servers window from the View menu or press CTRL + ALT + G. Expand the Database Engine node, then right click on the Local Server Groups folder and select New Server Registration. Enter the server connection information and close the window. The new server will appear under Local Server Groups. The server connection information is stored in an XML file and the server will be displayed in the Registered Servers window the next time you open SSMS.
2. Split the Query window to more efficiently work on large queries – It’s not immediately obvious but the SSMS Query Editor window can be split in two, enabling you to see two different sections of the same query simultaneously. Splitting the query window can be useful if you have a large query and want to view or work on different sections of the same query. Both parts of the split window can be scrolled and edited independently.
3. Speed up your editing using keyboard shortcuts – Taking advantage of keyboard shortcut can make your work faster and more efficient. SSMS provides the following built-in keyboard shortcuts that you’ll find handy.

• F5, CTRL + E or ALT + X — Execute the currently selected code
• CTRL + R — Show/hide the Results pane
• CTRL + N — Open a new query window
• CTRL + L — Display the query execution plan
• CTRL + SHIFT + Q– Display the query designer
• CTRL + I – Display the incremental search dialog

4. Script multiple objects by using the Object Explorer Details — Scripting objects like tables and stored procedures is one of the handiest features in SSMS. But not everyone realizes that you can also script multiple object at once. To script multiple objects you need to use the View menu to open the Object Explorer Details window. Now use Object Explorer to expand a database then navigate to one of the nodes like Tables, Stored Procedure or Views. The Object Explorer Details window will show all of the available objects allowing you to select multiple objects. Right clicking will display a context menu where you can select the Script As option.
5. Comparing queries and results using New Vertical Tab Group – The default layout of Query Editors tabbed windows makes it difficult to compare the T-SQL code and results that are in different windows. However, if you use the New Vertical Tab Group option from the SSMS Window menu you’ll be able display the contents of the different tabs vertically enabling you to much more easily compare the code and the query results.
6. Displaying line numbers for large queries – When you’re working with large queries it can be useful to display line numbers on the query window. To enable the display of line numbers use the Tools menu and select Options. In the Options dialog expand Text Editor and then select Transact-SQL and check the Line numbers box.
7. Select blocks of text using the ALT Key – Normally, when you select text with the mouse the Query Editors selects it in horizontal blocks. If you hold down the ALT key you can select columns and vertical regions as well as by lines. Using ALT + select can be particularly useful for deleting a block of text. For example, you might want to cut all of the schema references for a given tables T-SQL code.
8. Using GO X to execute T-SQL batches multiple times – Everyone knows you use the GO command to mark the end of a T-SQL batch and send it to SQL Server to be executed but did you know if you specify a number after the GO command it will cause the batch to be run the specified number of times? You can use this to repeat insert and update statements for creating test data and other commands to you want to run multiple times.
9. Refresh the IntelliSense cache for new objects – Sometimes when you first create a table or a stored procedure it won’t have any IntelliSense displayed for it. This is because the local IntelliSense cache needs to be refreshed. To refresh the IntelliSense cache use the Edit menu then IntelliSense and Refresh Local Cache. You can also use CTRL + SHIFT + R.
10. Add connection strings keywords for you connection – To connect to a SQL Server instance you typically use Object Explorer and select the Connect button and then fill out the connection information. However, you can also modify your connection by passing in different connection string keywords. To pass in connection string keywords you need to click the Options button on the Connect to Server dialog and then select the Additional Connection Parameters tab where you can enter connection string keywords.