10 SSMS Tips and Tricks to boost your Productivity

I have been using SSMS for a long time now, and over the period of time I have realized that it’s a very powerful IDE – however, there are lots of hidden features which are less known to lots of developers. In this article, I will be sharing some beneficial Tips and Tricks which will give you an extra edge in your day to day work, and in the process take your productivity to the next level.

I would highly recommend you to go through my last 2 articles relating with SSMS Productivity, if you have not gone through it already –

• SQL Server Management Studio 2016 Productivity Enhancements
• Increase your Coding Speed in SQL Server Management Studio

Use custom colors to differentiate between environments

During our day to day work in SSMS, generally we have to shift between different environments – Local/DEV/QA/MOCK/PROD, resulting in the creation of multiple query tabs – with each query tab connected to different environments. This often becomes unmanageable and difficult to keep track of. Also, think about a scenario when you have a data manipulation script to be executed in different environments, isn’t it a big advantage to have a visual differentiation between the environments you are executing your scripts against?

SSMS provides us with an ability to set different colors for connection to separate environments. Personally, I prefer to set the below colors for my environments —
PROD – Red, MOCK – Orange, QA – Yellow, DEV – Blue and Local – Green

The color is displayed in the SSMS status bar, at the bottom. Hence when you connect with a particular environment, it uses the same assigned color. This presents a visual indication of the environment in which you are running your scripts.

SELECT 'Local DB for Update/Delete/Insert' AS 'Environment' SELECT 'DEV DB for Update/Delete/Insert' AS 'Environment' SELECT 'PROD DB for Select' AS 'Environment'

When you are connecting to a SQL Server instance, you will need to click on the ‘Options’ button:

Then click on the ‘Connection Properties’ tab and you can choose a custom color for your environment:

Once you select a color for an environment, every time you login to that SQL Server instance – it will display the same color in the SSMS status bar. For developers running data manipulation scripts, it’s always nice to have a visual indication of the environment you are logged in – especially a RED color in your SSMS query editor, indicating you to be extra cautious.

Reuse your copied items by storing in memory

Two shortcut keys which every developer use in their day to day work is CTRL+C and CTRL+V. The problem with pasting your copied contents using CTRL+V is that it only pastes the last copied text. ‘Cycle Clipboard Ring’feature in SSMS allows you to keep track of last 20 items you have cut/copy and reuse them without any need of copying and pasting text multiple times.

You can use CTRL+SHIFT+V to paste the current item in Clipboard Ring’s memory. If you repeatedly press CTRL+SHIFT+V, you can actually cycle through all the entries in Clipboard Ring’s memory, and then select the item you want to paste in your code editor.

Edit multiple lines of code at the same time

SSMS provides a ‘Vertical Block Select Mode‘ feature by which you can select multiple lines, type code and it will reflect across all the selected rows. To leverage this feature, you need to hold down the ALT key, then left click on your mouse to drag the cursor over the text you want to select and type/paste the text you want to insert into multiple lines. Pressing the ALT key is important, since it indicates to the query editor to not select the entire line but to follow the mouse pointer/ arrow keys for any action.

Keyboard Shortcut – ALT + SHIFT + Arrow Keys
Mouse – ALT + Left-Click + Drag

Leverage built in Performance Reports in SSMS

SSMS provides a number of standard database level reports out of the box for performance monitoring and troubleshooting purpose. The best thing about these reports are that it encapsulates all the complexity behind generating them and displays the results in the form of graphical reports – which can be used in any of your presentations.

To access the reports, Right Click on the Database –> Go to Reports –> Go to Standard Reports –> Pick any of the SSMS Reports

For instance, the below ‘Disk Usage’ report displays the total amount of space that the AdventureWorks2008 is occupying and the free space available.

To view the details of the Standard SSMS Reports, please review the MSDN article here – SQL Server Management Studio Standard Reports – The Full List

Generate SQL Insert scripts using SSMS

To generate scripts for your database tables, SQL Server Management Studio provides a handy wizard called as ‘Generate and Publish Script‘. You can also use this wizard to generate insert scripts for your tables.

Right Click on the Database and go to Tasks -> Generate Scripts

Next, you can select the database object you want to script.

Be default, the wizard allows you to generate scripts for the table schema only. However, if you need to generate Insert scripts for your table data, you will need to navigate to the ‘Advanced Scripting Options‘ and change the ‘Types of data to script‘ from ‘Schema only’ to ‘Data only’.

Troubleshoot issues with Intellisense in SSMS

Sometimes when you create a database object, SSMS is not able to identify the newly created object and throws an error stating – ‘Invalid object name’

This is because of a simple reason that the SSMS Intellisense cache has not been refreshed. In such cases, you can use a simple shortcut CTRL+SHIFT+R to update the Intellisense cache. You can also navigate to this setting by going to Edit -> IntelliSense -> Refresh Local Cache.

This is a handy tip and can save you time, if you stumble into scenarios where intellisense does not function as expected.

Recover your unsaved SQL queries in SSMS

Most of you must have had instances where you lose your unsaved work – due to SSMS crashes or sudden machine reboots due to a windows update. It is very helpful to be aware of the ‘auto-recovery feature’ in SSMS where you can configure how frequently you want to save your work and how long you want to preserve the information. This is not a substitute to saving your work on a regular basis, but for sure can minimize your data loss and save rework time, in the case of unfortunate incidents.

You can also navigate to this setting by going to Tools -> Options -> Environment -> AutoRecover

Based on the above setting, SSMS tries to recover the unsaved queries and prompts a dialog box to the user, in the case of an unexpected SSMS shutdown.

If due to some reason, you so not get the file recovery prompt, you can navigate to the folder location below where the backup files are located.

Customize your environment for better productivity

When you are working in an IDE, it is essential to be aware of all the features/options you have access to. A superlative knowledge of the IDE helps you to code and work faster. By default, SSMS has a fixed number of buttons added to the toolbar, but you can always personalize the button bars as per your needs. Having all the frequently used functions right in front of you in the toolbar gives you quick access, helps you become more productive, focus on your work and get more stuffs done in less amount of time.

You can click ‘Add or Remove Buttons’ option in the toolbar to remove the features you rarely use, and have just a handy set of options available which you will use in your day to day work.

I removed few button controls, highlighted above in red, to customize my toolbar.

You can do the same by getting rid of features, which you do not use frequently. The result will be a much cleaner toolbar, on which you have complete control on.

Take assistance of online articles from within SSMS

While working on your code changes in SQL Server, you might need to refer the internet multiple times. With this handy tip, there is no need to leave SSMS and open a web browser to search for the required information. You can actually open a web browser from inside SSMS by using a keyboard shortcut CTL+ALT+R. You can also click directly on the ‘Web Browser’ icon on the toolbar.

For convenience purpose, you can also leverage the Horizontal/Vertical Tab Group Feature to have your SQL Query and Web Browser in side by side independent windows and perform your development work much faster.

Displaying Line Number in SSMS Query Editor

While working on a large SQL script or a stored procedure with long lines of code, it’s beneficial to have the line numbers displayed in the Query Editor. By default, displaying Line numbers in SSMS is turned off. This is sometimes frustrating because it makes the navigation between long lines of code tougher. Also, think about a scenario when you run a TSQL query and SQL Server throws you an error message referencing the exact location of the error (in this case Line 42). How convenient would it have been if the SSMS Query Editor displays the Line Number out of the box?

This is one of the hidden features of SSMS and you can actually display line numbers with all the earlier versions of SSMS starting SQL Server 2005. You can enable this feature by navigating to – –Tools -> Options -> Text Editor -> All Languages -> General -> Line numbers

Once you select the Line numbers checkbox, click OK and return to the Query Editor, you will see that line numbers have been enabled.

INSERT INTO [AdventureWorks2008].[Sales].[Currency]   (   [CurrencyCode],          [Name],          [ModifiedDate]      VALUES   (         'USD', 'US Dollar', '2016-12-01 00:00:00.000'   )

You can also leverage a pretty handy shortcut to easily jump to a particular line number – CTRL + G

Conclusion

SSMS has a number of hidden features which might not have been advertised much, but knowing these tips and tricks will make your life so much easier. I would highly recommend you to start using these tips and tricks in your day to day work, and you will realize the increase in your overall productivity and efficiency.

To further enhance coding productivity, try ApexSQL Complete, a free SSMS and Visual Studio add-in.

See more

To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.

References

• Generating SQL Insert Scripts in just 2 Clicks
• Tip SSMS: How to BLOCK Select
• SQL Server Management Studio Tips and Tricks