Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New Features in SQL Server Management Studio for SQL Server 2016


By:   |   Read Comments (4)   |   Related Tips: More > SQL Server 2016


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

You heard the news about the forthcoming release of SQL Server and its new features and improvements. One of the areas where there are enhancements is SQL Server Management Studio. In this tip I will show you those new features.

Solution

Every new version of SQL Server comes with a new release of the Management Studio. One of the reasons behind this is to give DBAs the graphical tools to administer the new features of each release. Since SQL Server 2016 adds a considerable amount of new features it is reasonable that we will have an interface upgrade as well.

SQL Server Management Studio Installation from the Internet

One of the improvements of this version is that you don't need the SQL Server DVD to install the client tools. Now the installation uses a web installer that can be downloaded from the internet, just like the SQL Server Data Tools. When you run the installer it will download the required packages to install.

Automatic SSMS Updates

Now SQL Server Management Studio automatically checks online for updates and gives you the chance to manually check for updates by clicking the "Check for Updates" option on the Tools menu. The next image is a comparison of the Tools menu in the 2014 and 2016 versions of SQL Server Management Studio.

New Check For Updates Option.

Live Query Statistics

Remember the new sys.dm_exec_query_profiles Dynamic Management View introduced in SQL Server 2014? If not, then you can read about what it can do for you when troubleshooting performance issues in my previous tip SQL Server 2014 Real Time Query Monitoring. As a quick reminder, this DMV given the circumstances shows the execution progress of a query, but that improvement didn't came with the corresponding graphical interface.

This new version of SQL Server Management Studio gives us the ability to graphically view the same results without the need to query that Dynamic Management View. You can see on the next image that a new button has been added to the SQL Editor Toolbar between the Include Actual Execution Plan and Show Client Statistics buttons.

Include Live Query Statistics Button.

Let's try this new tool by executing the following query.

SELECT 0 FROM sys.objects A
CROSS APPLY sys.objects B
CROSS APPLY sys.objects C

As you can see on the next image, it resembles an execution plan with a few differences. At the bottom of each operator we can see how much time it takes to complete and the percent of completion. Also notice that each operator is connected by a dashed line instead of the traditional lines.

Looking at Live Query Statistics.

Edit Top 200 Rows for Views

With previous versions of SQL Server Management Studio, when you try to edit rows from a view, SSMS crashes. This issue has been fixed. Of course, this option won't work for all views because not every view is updateable. As an example, a view with an aggregate function or derived columns is not updateable.

But if you try to modify a view's data from a non-updateable view by clicking the "Edit Top 200 Rows" option of SQL Server Management Studio, it will open the row editor, but when you try to save the changes an error message will pop up explaining why the modification cannot be made. The good thing is that SQL Server Management Studio won't crash.

Updating Views in SSMS.

SSMS Azure Integration

SQL Server has been increasing its cloud services for a while and this version of Management Studio goes forward in that direction. Now Table Designer is enabled for Azure SQL Database v12. Also the Database and Properties dialogs works with Azure SQL Database v12. Furthermore, the Import and Export Wizards now have support to Azure SQL Database service tiers.

Save Open Queries in SQL Server Management Studio

Another feature that its worth mentioning is the fact that now you are free to choose if you want SQL Server Management Studio to ask you to save your open queries when you quit the program. I think that this feature is mostly oriented to DBAs than Developers, but either way use it with caution. You can change this setting by selecting the "Options…" command from the Tools menu. After the Options window shows browse on the left side tree for "SQL Server" leave within "Query Execution" node. The next image should clarify the previously stated.

New Option to Prompt to Save Open Files.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, May 09, 2016 - 2:09:30 PM - Jiping Back To Top

In SQL Server 2016 installation you won't find SSMS, cause it is indipendent component, and free. Use SSMS 2016 to manage previous database instances is highly recommended.


Tuesday, September 01, 2015 - 5:35:53 PM - Kevin M Back To Top

Thanks Daniel!

That's a huge win!  We're just in the process of upgrading to SQL 2014 SP1 with the idea we'll upgrade to 2016 next year so it's great we'll be able to use SSMS 2016 in the mean time to take advantage of that feature.  We run a very large data warehouse and are constantly wanting to know where a query is in the process.

Thanks again!

Kevin


Saturday, August 15, 2015 - 11:21:27 PM - Daniel Farina Back To Top

Hi Kevin,

Yes it will work! I have tried it myself!

Thanks for reading!


Friday, August 07, 2015 - 1:49:17 AM - Kevin M Back To Top

Hey Daniel,

Thank you for the great article!  I was wondering if the Live Query Statistics in SSMS 2016 would work against a SQL 2014 instance since it has the dm view.  Have you had or would you have the time to check that scenario?

Thanks!

Kevin


Learn more about SQL Server tools