5 Lesser Known Features of SQL Server Management Studio 2016

SQL Server Management Studio (SSMS) is one of the most frequently used tools by SQL Server DBAs around the world. If you have used the latest version – SSMS 2016 – then you have probably noticed that the look and feel changed: It is now designed to use the Visual Studio 2015 shell. Along with this change are several other updates that aren’t well-known to most users:

1. Database Name Filter: With the latest version of SSMS 2016, we now have the ability to use a filter for Database folders. In earlier versions, this capability was limited to tables, stored procedures, etc. This feature is useful for those clients who have multitenant hosting SQL Server. Rather than scrolling through the long list, you can add a filter as shown below:

   

2. Releasing and patching changes: Microsoft has shifted the patching methodology for SSMS from the 2016 version so it is no longer part of the SQL Server product setup. It needs to be installed separately by downloading it from the internet. Here is the link to download SSMS: Download SQL Server Management Studio (SSMS)

   Along with the download, the frequency of patches of SSMS has also changed. Based on the past few download patterns, it looks like Microsoft is pushing updates to SSMS every month. This means that issues are getting fixed sooner rather than after a Cumulative update of SQL Server in the normal release cycle.

   We can also use SSMS to check for updates, if internet connectivity is available. To do that, we need to click on menu option Tools > “Check for Updates…”

   

3. Track code via Scrollbar: If you have used visual studio before then this is not necessarily something new, but if you are new to visual studio, you will love this feature. Using this feature, we can preview the code and track syntax errors in the code. We can use “bar mode” or “map mode” for the vertical scroll bar in the query window.
   • Bar Mode: This is the default and normal vertical scroll bar – the same as in the previous versions
   • Map Mode: A source overview can be viewed on the scroll bar which can again be configured as:
     • Off: Source Overview will not be visible
     • Narrow: Overview with minimum width
     • Medium: Wider than previous
     • Wide: Widest scrollbar with overview

    
   We can also configure to show Tooltip on hover or scroll using the checkbox “Show Preview Tooltip”. Go to: Tools – Options – Text Editor – All languages – Scroll Bars – Behavior. We can also open the same configuration window by right clicking on the vertical scroll bar and then clicking “Scroll Bar Options…”

   Here is how the feature looks:

   

   Also, notice a special outline for the line where we have a cursor. This is called “Highlight current line” and can be controlled by setting the option under Tools > Options > Text Editor > General > Highlight current line.

4. Find and replace dialogue: In this version of SSMS, many things have been inherited by Visual Studio because the shell of SSMS has been changed. Here is the new find and replace box.

   

   The Find and Replace box appears in the upper right corner of the SSMS. As shown in the image, it immediately highlights every occurrence of the given search string in the current document. We can navigate from one occurrence to another by choosing the Find Next button or the Find Previous button. We can also use the same windows to replace. We can also change the scope of the search and use regular expressions.

5. Search box in the options: Since there are so many settings to change, it would be difficult to remember all those settings. To help us, there is a search box added in the Tools > Option as shown below.

   

 
Along with above, there are many more features which you can explore by going to Tools > Option and look at various settings possible.

These new features, the new shell, and the new way of releasing patches to SSMS will make the life of DBAs and Developers much easier.