Seven Secrets in SSMS for Developers

Total: 2 Average: 5

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.

SSMS - Connecting to Multiple Instances
Figure 1: Connecting to Multiple Instances

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.

Minimizing Object Explorer
Figure 2: Minimizing Object Explorer

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:

Enable Line Numbers
Figure 3: Enable Line Numbers
Line Numbers Enabled
Figure 4: Line Numbers Enabled

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:

Removing Single Quite with Block Select I
Figure 5: Removing Single Quite with Block Select I
Removing Single Quite with Block Select II
Figure 6: Removing Single Quite with Block Select II
Block Select in SSMS
Figure 7: Block Select in SSMS

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
go

It 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:

SSMS - Enable Set Statistics Options
Figure 8: Enable Set Statistics Options

Note that you will need to launch a new query window to see the effects after enabling these options.

SSMS - Output of Set Statistics
Figure 9: Output of Set Statistics

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:

Set Implicit Transactions ON
Figure 10: Set Implicit Transactions ON

Setting Implicit Transactions ON saves any SSMS user from errors since you can issue ROLLBACK in case of a mistake.

Prompt to Commit Transactions
Figure 11: Prompt to Commit Transactions

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

Kenneth Igiri
Latest posts by Kenneth Igiri (see all)

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.