Cheap SQL Server 2016 Hosting Tutorial – Features in SQL Server Management Studio for SQL Server 2016

Cheap SQL Server 2016 Hosting Tutorial – Features in SQL Server Management Studio for SQL Server 2016

CheapASPNETHostingReview.com | Cheap and reliable SQL Server 2016 hosting. SQL 2016 has a number of major enhancements which will help whether or not you are implementing on-prem, in Azure or in a hybrid model. In a slight contrast to other recent releases of SQL Server, 2016 is not as directly focused on Azure features mainly because Microsoft is moving towards a common code base for both the on-premises version of SQL Server and Azure SQL Database which will allow for changes to come into the product in more of an Agile fashion.

For example, row-level security (RLS) and Dynamic Data Masking have both been in preview in Azure SQL DB for several months now and are included in this CTP. So effectively, Azure SQL DB gets the new toys first where they get tested out, and then the on-premises products benefit by getting features more quickly (in service packs or cumulative updates). This rapid development cycle is great for development shops who are requesting the new features — but where does it leave large enterprises who have to deal with testing cycles and regulation to upgrade versions? Don’t worry, Microsoft has a feature to meet your needs as well.

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.

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.

3683_Updates

Live Query Statistics

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.

3683_LiveQueryStatisticsBtn

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

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.

3683_LiveQueryStatistics

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.

3683_ViewUpdate

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.

3683_SaveFiles

I hope this article helpful for you 🙂

Note :