Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Management Studio Enhancements - Part 2


By:   |   Last Updated: 2016-10-21   |   Comments   |   Related Tips: More > 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

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

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

These are the scroll bar options:

SQL Server Management Studio Scroll Bar Options

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

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

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

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

Show marks

This shows the locations of breakpoints in the script.

SQL Server Management Studio Show Marks

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

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

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

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

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

Here we can see the differences of each of these options.

SQL Server Management Studio Scroll Bar Settings

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

Suppose we want to filter the database where database name is like "wide".

Database Name Filter Settings for Wide in SQL Server Management Studio

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

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

Suppose we want to filter the database name where owner equals 'sa':

Filter databses in SQL Server Management Studio by database owner

We now only see databases where owner is "sa" as shown below.

Databases owned by sa in SQL Server Management Studio

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

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

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
Next Steps


Last Updated: 2016-10-21


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools