By: Rajendra Gupta | Comments | Related: > SQL Server Management Studio
Problem
In my previous tip SQL Server 2016 Management Studio Enhancements we have seen some of the new SQL Server Management Studio (SSMS) features and improvements. In this tip we will explore some more enhancements in SSMS.
Solution
In my previous tip we have explored the following:
- Highlighting the current line in SQL Server Management Studio
- Searching for options in SQL Server Management Studio
- SQL Server Management Studio Quick Launch
- SQL Server Management Studio Tabs Customization
- Pinning Tabs in SQL Server Management Studio
Now we will explore these features:
- Scroll bar enhancements
- Filtering in the Databases node in SSMS
- Enhancements in offline database management studio
- Saving open queries in SQL Server Management Studio (SSMS)
Note: In this tip I am using the SQL Server Management Studio August release version 13.0.15700.28 with SQL Server 2016 version Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64).
![SQL Server Management Studio Version](/tipimages2/4492_SSMS.jpg)
SQL Server Management Studio Scroll Bar Enhancements
SQL Server 2016 Management Studio provides great enhancements for the vertical scroll bars with features like visuals, colors and tool tips. To open the scroll bar properties go to Tools > Options > Text Editor > All Languages > Scroll Bars tab or use the quick launch to search.
![SQL Server Management Studio Quick Launch for Scroll Bar Options](/tipimages2/4492_ScrollBars.jpg)
We can also open it by right clicking on the vertical scroll bar in a query window and select the Scroll Bar Properties... as shown below.
![SQL Server Management Studio Scroll Bar Options](/tipimages2/4492_vertical_scroll_bar.jpg)
These are the scroll bar options:
![SQL Server Management Studio Scroll Bar Options](/tipimages2/4492_scroll_bar_properties.jpg)
As we can see in the scroll bar properties, there are four annotation options: Show changes, Show marks, Show errors, Show caret position. I opened a query and we will look at each of these below.
Show changes
This annotation tracks all changes in the script. The green color on the right indicates that no changes were made in the script.
![SQL Server Management Studio indicating no code changes](/tipimages2/4492_annotation_tracks.jpg)
If we made any changes in the script it will appear yellow in the vertical scroll bar.
![SQL Server Management Studio Code Changes Highlighted in Yellow](/tipimages2/4492_changes_in_the_script.jpg)
Show Errors
If there are any errors in the script, it will show as red in the vertical scroll bar.
![SQL Server Management Studio Show Errors](/tipimages2/4492_syntax_error.jpg)
Show caret position
This displays the current location of the cursor in relation to the entire script. It is displayed as a blue line in the right vertical scroll bar.
![SQL Server Management Studio Show Caret Position](/tipimages2/4492_caret_position.jpg)
Show marks
This shows the locations of breakpoints in the script.
![SQL Server Management Studio Show Marks](/tipimages2/4492_vertical_bar.jpg)
The vertical scroll bar has two modes as we can see in the Behavior section of scroll bar properties.
![SQL Server Management Studio Behavior Settings for Scoll Bar Properties](/tipimages2/4492_two_modes.jpg)
The default value for vertical scroll bar behavior is Bar mode which can be seen in the above pictures. If we change it to Map mode, the vertical bar looks like this:
![SQL Server Management Studio Code in Map Mode](/tipimages2/4492_Default_value.jpg)
In the map mode, we have the Show Preview Tooltip option under the Behavior section. When this is selected a code preview appears when we hover over that portion of the vertical scroll bar.
![SQL Server Management Studio code preview](/tipimages2/4492_PreviewTooltip.jpg)
This is actually a good enhancement specially in the case of large queries. We can simply see a preview of part of the script just by hovering over the vertical bar. If we wish to move to that portion of the script, click on it or right click on the vertical scroll bar and select Scroll Here and it will move to that location.
![SQL Server Management Studio Scroll Here option](/tipimages2/4492_large_queries.jpg)
We can also control the width of vertical map scroll bar by selecting the source overview as Off, Narrow, Medium and Wide.
![SQL Server Management Studio Scroll Bar Settings](/tipimages2/4492_control_width.jpg)
Here we can see the differences of each of these options.
![SQL Server Management Studio Scroll Bar Settings](/tipimages2/4492_map_scroll_bar.jpg)
The Map vertical scroll bar mode also supports the annotations shown above.
Filtering in the Databases node of SSMS
SQL Server Management Studio now supports filtering databases in the SSMS object explorer. This filter can be done with the Database Name, Owner and Creation Date. To filter the database in SSMS, right click on Databases > Filter > Filter Settings.
![Filtering in the Databases node of SSMS](/tipimages2/4492_Databases.jpg)
Suppose we want to filter the database where database name is like "wide".
![Database Name Filter Settings for Wide in SQL Server Management Studio](/tipimages2/4492_database_name.jpg)
After applying the filter, we can see only databases having name like wide (i.e. WideWorldImporters and WideWorldImportersDW). The database node also shows that a filter is applied by showing (filtered) next to the Databases folder.
![Filtered Databases in SQL Server Management Studio](/tipimages2/4492_apply_filter.jpg)
The filter options for Name include Equals, Contains and Does not contain. For Owner it includes Equals, Contains and Does not equal. For Create Date there are bunch of different options.
![Filter settings in SQL Server Management Studio for database name](/tipimages2/4492_filter_database.jpg)
Suppose we want to filter the database name where owner equals 'sa':
![Filter databses in SQL Server Management Studio by database owner](/tipimages2/4492_database_owner.jpg)
We now only see databases where owner is "sa" as shown below.
![Databases owned by sa in SQL Server Management Studio](/tipimages2/4492_object_explorer.jpg)
If we want to remove the filter, right click on Databases > Filter > Remove Filter.
Enhancements for Taking a Database Offline in SSMS
Suppose we want to take a database offline using Management Studio. In the past if there were active connections the database will continue waiting for the sessions to be released. SQL Server 2016 Management Studio now supports a Drop All Active Connections option as shown below.
Suppose we want to take database the 'WideWorldImporters' which has active connections, right click the database and select Tasks > Take Offline. This will open this dialog box and we can see in the Message we have 1 active connection.
![Enhancements for Taking a Database Offline in SSMS](/tipimages2/4492_Take_offline.jpg)
We can click on the check box for Drop All Active Connections and it will now proceed without waiting and database will go offline once we click OK.
![Drop all active connections when taking a database offline](/tipimages2/4492_T-SQL_files.jpg)
Option to skip prompt for saving opened T-SQL query windows
Normally if we are closing new query windows, SSMS prompts whether we want to save the query changes or not. If we select no, it will quit without saving the file.
Suppose we do not want to have this prompt and want to close the query windows without this prompt. To do so go to Tools > Options > Query Execution > SQL Server > General and unselect the check box for Prompt to save unsaved T-SQL query windows on close.
![Option to skip prompt for saving opened T-SQL query windows in SQL server management studio](/tipimages2/4492_save-T-SQL_files.jpg)
Next Steps
- Download and explore SQL Server 2016.
- Check out SQL Server 2016 tips.
- Read more about SQL Server 2016 Technical Documentation.
- Read more about SQL Server management studio August release.
- Check out the SQL Server Management Studio tips.
About the author
![MSSQLTips author Rajendra Gupta](/images/Rajendra-Gupta-20220116.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips