New Features in SQL Server 2016 Service Pack 1
The SQL server team have came up with an innovative and useful idea of embedding a cloud strategy. With day-to-day increase in digitization scenario, the team had developed a new package of the SQL server i.e., SQL server 2016 Service Pack 1 (SP1). This application is designed in such a way that one can build enhanced programs, which could increase its range across editions and cloud. Therefore, in the following section, we are going to cover What are the new features in SQL server 2016 service pack 1 and what all major updates or additions are made in this SP1.
Tip: New Features in SSMS for SQL Server 2016
Feature #1: Create or Alter reference link
The SQL server 2016 provides a new syntax within it i.e., DROP IF EXISTS. This syntax enable users to execute DROP command even in absence of any object. In SP1, one can achieve CREATE OR ALTER for modules because it enables users to make use of the database script for a trigger, view, function, or procedure without any present existence. If present logic problem executes a DROP and then a CREATE command then, this will perform operations without compromising any permissions or dependencies. Moreover, this batch could be executed multiple times without any error.
Feature #2: New Query Option
A new query option named as OPTION(USE HINT(‘’)) is build to fasten the optimization procedure with help of query level hints that are given in tabular form.
These hints options are supported to permit a function that was previously available only with trace flag. In addition, this option does not require any sysadmin privileges.
Feature #3: Profiling of per-operator query
It reduces the performance of gathering per-operator query statistics like actual number of rows. This updated feature could be enabled either by operating global startup i.e., TF 7412 or can automatically be turned on when a XE session containing query_thread_profile is activated. Whenever this lightweight profiling is enables, the entire information is also present in sys.dm_exec_query_profiles allowing users to activate a feature in SSMS i.e., Live Query.
Feature #4: Improved Relation Between DMVs & Diagnostics XE
Two SQL queries i.e., Query_hash and query_plan_hash are used to identify a query uniquely. DMV shows these commands as varbinary(8) whereas Xevent shows them as UINT64. Since the SQL server is not having unsigned bigint therefore, casting on the server does not work, regularly. When talking about improvement, a new query named as Xevent action is introduced. The working of this is equivalent to query_plan and query_plan_hash, except the fact that they are defined as INT64, which will be helping in correlating commands between DMVs and XE.
Feature #5: DROP TABLE Support For Replication
Now one can drop a table, if the allow_drop property is enabled on all publication, which are having table(s) as an article. If this property is customized to FALSE then, DROP TABLE operation will get failed and display that replicated articles can not be dropped.
Feature #6: Enhanced Visibility of tempdb
On initialization of service, a composed message gets written to the error log that is indicating when system data files for tempdb are improperly configured with equivalent size or settings of autogrow. The message also represents files number so that issue is easy to track too. Therefore, this enhancement will help in tracking the reason of errors, which occurs while working with the server.
Feature #7: Database Cloning
Clone database is a new DBCC command, which permits database administrators and support team to debug the current problem related to production of database, by cloning schema and metadata. However, it is not meant to be used in production environment. If in-case database is generated from call to clone database then, one can use following command:
The value returned after execution of this command can either be 1 for true or 0 for false. A CLONED database will always be containing schema and by default one will be containing the statistics and query store data.
Feature #8: Quieter In-Memory OLTP
Another new feature in SQL Server 2016 service pack 1 is now having logging to SQL error log for helping support troubleshooting In-Memory OLTP. Well, in few cases this was flooding the log. Therefore, this feature is now set back to default in SP1 that users should observe the log revert to learn behavior of the server.
Observational Verdict
The SQL server 2016 service pack 1 new features are described for end users to know benefits of it. Moreover, users can now experience a new version of the server and hence, utilize application for more different purposes.