SQL Server Management Studio is Microsoft’s primary client tool for interacting with SQL Server. It is simple, and it has been around for quite a while (launched with SQL Server 2005). Still, it is easy to miss plenty of great features that can make the T-SQL scripts execution and overall interaction with SQL Server more rewarding.
In this article, we shall pay attention to seven of these capabilities. It might seem strange, but they often remain hidden even from experienced administrators who work with SSMS. Let’s set the ball rolling.
1. You Can Install SQL Server Independently
Back in the days when I started supporting SQL Server in Ghana, I noticed that many of the application support staff considered it was necessary to invoke a remote desktop session on a database server to access the database. It turns out that you can interact with a database engine remotely using the SQL Server Management studio.
Starting at SQL Server 2014, Microsoft made the separation between SSMS and the database Engine even more explicit. From an installation of SSMS on your workstation, you can access several instances of your SQL Server. At the network level, ensure the absence of any firewall rules blocking your access to the necessary port on your instances.
When you use SQL Server Management Studio to access multiple instances, be extremely careful to avoid making changes in the wrong instance. Here, the wrong instance means the one you did NOT intend to modify.
2. Installing SQL Server Installs Drivers
When you connect to the SQL Server instance either locally or remotely, it is not the nice-looking GUI that establishes the session. It is the job of SQL Drivers. Each version of SQL Server Management Studio comes along with SQL Drivers bundles.
Once, while troubleshooting a connectivity issue introduced by applying TLS 1.2, we updated the drivers simply by installing a more recent version of SQL Server Management Studio. This update upgraded the drivers, and we solved the problem.
It is worth mentioning that now you can get connection libraries from the Microsoft website without installing SSMS.
3. Getting More real estate
During the work with SQL Server Management Studio, you may realize that the Object Explorer pane is consuming too much space. You need to know more.
Click the pin shown in Figure 2, and viola! You can type the SQL statements in white space.
4. Enable Line Numbers
The Tools > Options section in SSMS comes with a range of configuration options worth exploring. By selecting the respective checkbox, we can enable line numbers. It simplifies the code troubleshooting:
5. Block Select
Anyone who used simple text editors with advanced features is familiar with block select. It turns out that SSMS also comes with this option. It is handy when you need to edit several code lines in one swoop:
6. Set Statistics IO and Set Statistics Time
SET STATISTICS IO and SET STATISTICS TIME are two well-known commands for capturing the queries performance statistics at runtime. The typical way to use these commands is shown in Listing 1:
-- Listing 1: Set Statistics
set statistics io on;
set statistics time on;
use TSQLV4
go
select * from Sales.OrderDetails
goIt turns out that these options are also available in the GUI under Tools > Options > Advanced. When set, the output returns information about the execution time and impact on IO:
Note that you will need to launch a new query window to see the effects after enabling these options.
7. Implicit Transactions
Setting Implicit Transactions ON means you must explicitly commit or rollback the transaction. When set to OFF, you are in auto-commit mode.
In SSMS, IMPLICIT TRANSACTIONS OFF is the default, but we can manually set the option at Tools > Options > ANSI. Once we do this, SSMS requires you to commit or rollback every statement explicitly:
Setting Implicit Transactions ON saves any SSMS user from errors since you can issue ROLLBACK in case of a mistake.
Conclusion
This article enlightened seven impressive features of SQL Server Management Studio. They are handy and beneficial to all developers, DBAs, and other users. There are still many other options in this tool that one can use to make coding with T-SQL more efficient.
References
SQL Connection Libraries
Implicit Transactions
