SSMS provides access for managing and solving queries in SQL. However, besides its primary and most demanded options, it includes lots of tips and tricks that you might not be aware of. They help enhance the productivity of your work, though.
In this article, we’ll describe those tips you can use in SSMS to increase your efficiency and reduce the time spent on routine.
1. To Differentiate Between Environments, Use Custom Colors
SSMS has different environments like Local/ MOCK/DEV/PROD – they are the processes of developing a successful product. As a result, you got multiple query tabs, and it is often confusing.
It isn’t easy to manage different environments. Just think of a scenario where you have an environment and need to script the data in a different one. Here, coloring these environments with different shades becomes a significant advantage – it creates visual differences between those environments where the data can be scripted.
It displays the color at the bottom of the SSMS status bar. You can also write scripts in the same color and thus improve the visual experience.
2. Reuse Copied Items
Every developer uses two shortcuts most frequently: CTRL+C and CTRL+V – copy and paste. However, “copy and paste” allows you to paste the last copied item only. It is problematic when you need to copy and paste a lot of information or program.
Cycle Clipboard Ring is a feature in SSMS that tracks the last 20-minutes’ activity of cut/copy. With its help, you can reuse these fragments without copying multiple times. CTRL + SHIFT+ V is the shortcut for pasting the stored memory from Clipboard Ring. If you repeatedly use CTRL+SHIFT+V, you can revisit all the entries copied and select the one you need from them.
This trick saves a lot of valuable time and allows the developer to focus more on product development.
3. Edit Scripted Program
One more helpful thing that SSMS provides to the developer is a “Vertical Block Select Mode.” This feature allows you to select multiple lines for editing. It also allows you to type codes that will reflect on selected rows.
To run the feature, press ALT in the keyboard, and hover the cursor over the codes and rows you want to select for copy/paste or insert multiple lines. It is mandatory to press ALT, as it helps to indicate the particular query and won’t choose the entire line. To follow up on the query, drag the cursor and tap for editing the query.
The keyboard shortcut for applying the feature is ALT+ SHIFT+Arrow Keys. If you want to use the mouse and keyboard, the shortcut is ALT+ Left click on mouse+ Drag.
4. Leverage the Profit of Built-In Performance Reports
A performance tracker with access to the database and a performance monitoring option is fruitful for any developer. SSMS has a feature that provides many reports at the standard database level, which helps performance monitoring and troubleshooting.
Such reports have other advantages too. The best thing is summarizing the complexity that went behind generating the result. Also, it gives a graphical representation of this summary.
This feature is excellent when you want to release data to clients. Also, you can use it in the presentation to track the growth of the project.
To access this feature, right-click on the database, then go to Reports and Standard reports. From there, you can pick any report.
5. Generate SQL Scripts
This trick is valuable for every developer. SSMS provides a handy wizard for generating scripts for database tables – the Generate and Publish Script feature/script.
To use it, right-click on the database and go to Tasks. Then proceed to the “Generate scripts” option, and get pre-loaded scripts from SQL.
By default, you are allowed to generate scripts for table schema only. If you want to create and insert scripts in the table, navigate “Advanced Scripting Options.” There, you can change “types of data to script” from “Schema only” to “Data only.”
These changes help you to customize your needs and leverage the profit of the SQL management studio.
6. Troubleshoot Issues in SSMS
When a new database object is formed, it is difficult for SSMS to identify the database. As a result, it recognizes it as an error stating it as an “Invalid object name.”
It is one of the troubleshooting issues that happen in SSMS. This problem occurs for a simple reason: the SSMS Intellisense cache is not refreshed after inserting a new database.
To resolve this issue, use a keyboard shortcut: CTRL+ SHIFT+ R. It helps refresh the cache. If your SSMS does not work correctly and produces troubleshoot issues consistently, keep refreshing the Intellisense cache. This handy tip will save your time and nerves.
You can also navigate to the settings you are going to edit and then move forward to Intellisense. From there, move ahead to refreshing the local cache.
7. Recover Unsaved SQL Queries
Have you ever lost unsaved data? Well, it happens to everyone, and if you can’t recover unsaved information, you get a new burden of additional work.
One of the best tricks in SQL Server Management Studios is recovering unsaved data. If you lost your work due to SSMS crashes or sudden reboots or updates, you can use the auto-recovery feature of SSMS and get the lost job back. It can save you hours!
You can configure how frequently you want to save your data and how long you want to preserve information.
However, it is not a replacement for regular data saving. Think of this possibility as an extra cherry on the cake – if you lose something because of some unfortunate accident, you can recover it. Still, configure regular saving the data too.
To set up the recovery feature, navigate to Tools > Options > Environment > Auto recover.
8. Customize Your Environment for Better Productivity
Productivity is the key to success. To get a better work environment, you can customize your space by changing the default settings.
By default, there is a fixed number of buttons in the toolbar, but you can personalize them. When you have all the functions right in front of your eye, it becomes easier to access them. It makes you more focused and helps to do tasks in less time.
To personalize your environment, click on “Add or Remove Buttons” in the toolbar. There, you can remove the buttons which you rarely use, and add those buttons that are essential and helpful. This way, you configure your personal set of handy options for daily work.
9. Take Assistance from Online Within SSMS
While working on code changes in the SQL server, you can use multiple references on the Web. The good news is, you don’t need to leave SSMS and launch a separate browser window for that. Instead, you can open a web browser, staying in SSMS.
The keyboard shortcut is CTRL+Alt+ R. You can even use a web browser toolbar to access features of SSMS.
For your convenience, you can apply the “Horizontal/Vertical Tab Group” to have both the one-side or side-by-side view. On one side, you can see the SQL query, and the other one will be a web browser.
10. Displaying Line Number in SSMS
When you work on large SQL scripts with long lines and codes in SSMS, it is very convenient to have a line number. It ensures fewer errors and helps to work better. However, the default reading line number is disabled in SSMS, which is annoying, as you have to remember the line number in long codes.
For instance, you need to edit code in line 40. Do you think it is possible without knowing the count of the line you are reading?
There is a hidden feature that enables showing the line number. It saves a lot of time and solves one of the biggest problems in SSMS.
Navigate to the tool, then to Options, and, eventually, to the text editor. There, move forward to languages > general > line numbers.
Conclusion
SSMS has a plethora of hidden tips and tricks, making the working process more comfortable. You can use the information provided in this article for better performance daily. These tips give your work extra edge and save you a lot of time, effort, and nerves.
Tags: database administration, sql server, ssms Last modified: September 20, 2021
