New Features in SQL Server Management Studio for SQL Server 2016

By: Daniel Farina | Updated: 2015-08-06 | Comments (4) | Related: > SQL Server 2016

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.

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.

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.

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.

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

You can download a copy of SQL Server Management Studio 2016 for free from this link: https://msdn.microsoft.com/en-us/library/mt238290.aspx.
For more tips about SQL Server Management Studio check out SQL Server Management Studio Tips Category.
Stay tuned to the SQL Server 2016 Tips Category for more information about this release of SQL Server.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2015-08-06

Comments For This Article

Monday, May 9, 2016 - 2:09:30 PM - Jiping	Back To Top (41438)
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 1, 2015 - 5:35:53 PM - Kevin M

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

Hi Kevin,

Yes it will work! I have tried it myself!

Thanks for reading!

Friday, August 7, 2015 - 1:49:17 AM - Kevin M

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