10 New Features SQL Server 2016 Does Better than SQL Server 2014

1. Query Store

Microsoft comes up with a unique feature on SQL server 2016 as “Query Store”, which will store the history of query execution with their performances, statistics, and areas of improvement in terms of a query. If there is any change in the performances, the Query Plan will take a snapshot of it to alert the administrator or developer regarding the change in the query performances to review them completely. As a frequent task, any issue encountered in the query will be simultaneously recorded in the Query Plan. Doing so can help the admins to fix a newer query with regards to the performance resource problem. It is implemented at the database level in SQL Server 2016.

2. Row Level Security

Basically, when a user data access is given, in SQL Server 2014, the access is given for the entire table with either read or write permissions. An additional feature in SQL Server 2016 gives a profound level of security to filter and view the data for specific users. This can avoid falling the data and information in the wrong hands. Though there are many stored procedures used in the industry to filter the data for specific ID and user, a new feature in 2016 brings fail-proof row-level security.

3. Always Encrypted

Further moving on to security level in data, a new feature in SQL Server 2016 adds up encryption level at every mode of transaction. The encryption is carried out when the database alive, during the transit and even during the resting time. This can primarily help trespass the data present in the database. However, the entire database is not being encrypted but is with the column level. A new feature called “Client Library” is used to retrieve the data, rather than retrieving directly from the database.

4. In Memory OLTP Improvements

In-Memory OLTP is a feature in SQL Server 2016, which can get the most of workloads from SQL Server. It helps with vast support for foreign keys, unique constraints, and parallelism. Additionally, providing tables up to 2 TB storage support. The primary improvements have been in transaction speed and scalability. Being doing OLTP improvements, the latest database architecture that needs more memory size and many-core performances could be compromised. The IT infrastructure costs can come low with the use of In-Memory OLTP.

5. Polybase

Originally introduced by Microsoft as “SQL Server Parallel Data warehouse”, which was used in SQL Server 2008 R2. The recent update is put forward in SQL Server 2016 as “Polybase” that is used to communicate and interact with the Hadoop data-carrying which is broken and semi-structured. Used to query both relational and non-relational databases. Further, the data can be stored in Azure Blob Storage.

6. Dynamic Data Masking

A fantastic feature introduced in SQL Server 2016, which allows users from being restricted to a certain level of reading access to columns. Obscuring the data is the priority in the Dynamic Data Masking feature. Say, an account number is stored in an object and a restriction has to be put on the object by making sure just a few digits of the last number have to be shown for a group of people or a specific person. Turning on the Dynamic Data Masking, we can achieve the target of just showing the last few digits of the account number. Further, by deactivating the feature for the rest of the folks, the account number is made visible completely. This can add a lot more security by preventing malicious developers and admins.

7. Multiple TempDB Database Files

During the installation setup, multiple TempDB database files are created to improve the performance based on the physical configuration of a computer. Intelligently, it detects the number of processors present and based on the presence the TempDB database files are created. About 8 TempDB data files are created in case if the process is core i3 and above during the setup of SQL Server 2016.

8. Live Query Statistics

Live Query Statistics is another awesome feature fused in SQL Server 2016, which helps real-time monitoring of active query for instant debugging and fix. We can exactly specify the issue since the debugging can be done even before the query is executed completely. Properties such as time-frame, operator progress, and so on help in Adhoc risk management.

9. Enhanced AlwaysOn

By replacing the prior feature of Mirroring, Microsoft has come up with a new feature called “AlwaysOn Availability Groups”. It helps in providing two database instances to be exactly the same in terms of transactions and data with completely zero data-loss. A lot of leverage has been put forward to duplicate the database instances with the help of an enhanced AlwaysOn concept.

10. Stretch Database

Using SQL Server 2016 Community Technology Preview 2(CTP2), the entire database can be migrated to the Microsoft Azure cloud without archiving the data. A new option called “remote query” will help to retrieve the query execution without retrieving the data directly from the server that leverages the processing power.