New Features In SQL Server 2016

Introduction

SQL Server 2016 has many new features for database administrators. This version of SQL Server is not directly focused on Azure support. The following are key features that are introduced or enhanced with SQL Server 2016.

Always Encryption: This feature of the SQL server enables us to encrypt the SQL Server database. This means that data is always in encrypted form in SQL Server. Access to this encrypted data is available by calling the application. SQL server is now supported by both column level as well as table level encryption. It allows a client application to access which has an encryption key. This encryption key is not passed to the SQL Server. This feature also works with the cloud.
JSON Support: JavaScript Object Notation is now supported by SQL Server 2016. Now we are able to interchange JSON data between applications and database engines. SQL Server engine has ability to parse JSON data. Some new functions have been added to provide support for querying JSON data. It is very similar way to XML support, it is built in with "FOR JSON" and "OPENJSON" functions.
Row Level Security: Row level security enables the database engine to restrict access to row data which is based on server login. By using filter predicates that define the inline table value function, restricting rows will be done. This is ensured by Security policies that filter predicates get executed for each and every SELECT or DELETE operation. With this feature of SQL Server 2016, users need not take care to restrict data from some login by writing code. This will maintain the database engine itself.
PolyBase: Big Data and Hadoop have been introduced for handling large amounts of data. Microsoft has introduced PolyBase which allows us to query distributed data sets. Using PolyBase, we are able to use T-SQL to query Azure blob storage and Hadoop. PolyBase enables us to write ad-hoc queries to join semi-structured data stored like Azure blob storage and Hadoop to relational data from SQL Server. This allows us to retrieve data from semi-structured data stored without knowing it.
Temporal Table: Temporal Tables are tables that hold the old version of rows within the base table. If the database has a Temporal Table, the SQL engine automatically moves the old version of the row to the temporal table whenever it is updated. The temporal table is physically a different table than the base table, but it is internally linked with the base table.
Dynamic Data Masking: If we want to restrict someone from seeing your confidential data, this feature of SQL Server 2016 is very important. Using Dynamic Data Masking, we can mask the confidential columns of data in a table for users who are not authorized to see these data. For example, if we store the mobile number or email address of the user in our table but want to restrict a user to see the whole mobile number or email address it might be able to see some part of the mobile number or email address. At this time we can use Dynamic Data Masking. To set up this feature, we need to set the masking rules so unauthorized logins can only read some part of the mobile number or email address and authorized logins can see the whole mobile number or email address.

Multiple TempDB Database support at the time of installation

Best practice says it would be good to have multiple tempDB files if the database server running on a multi-core machine. Up to SQL Server 2014, we need to create additional tempDB data files manually once installation is done. In SQL Server 2016, we can configure the number of tempDB files at the time of installation.

Query Store: SQL Server 2016 database engine now maintains a history of query execution plans with performance data and it also able to find queries that have had slower performance recently. Prior to this version, we could examine execution plans by using DMVs (dynamic management views) but it only allows us to see the plans which are active in the cache. This feature is now enabling us to examine historical execution plans and also save query statistics.
R-integration: SQL Server 2016 now supports in-database advanced analytics. Using this feature we can execute R code inside the SQL Server database engine.
Stretch Database: This feature of SQL Server 2016 allows us to dynamically archive data in a secure manner from our local database to Azure SQL database hosted in the cloud. When we enable a database to stretch the older data is dynamically moved over to the Azure SQL database. When we run a query on the database, it would access the active records first (local database) and then historical data from the stretched database. This feature enables us to archive data with any change in application code.
In-Memory Enhancements: SQL Server 2014 introduced the concept of "in-memory" tables. These were designed for high-speed loading of data with no locking issues or high-volume session state issues. This feature has a lot many limitations. In SQL Server 2016, this feature is enhanced to support foreign keys, checks, unique constraints, and parallelism. SQL Server 2014 supports in-memory tables up to 256 GB but it now extends to 2 TB.

Summary

SQL Server 2016 comes with many more new features and enhancements. This version of SQL Server has improved probably each feature of the previous version for example column store indexes, the new feature of operational analytics, etc.