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 SQL server enables us to encrypt the SQL Server database. This means that data is always in encrypted form in SQL Server. Access of this encrypted data is available to calling application. SQL server is now supported both column level as well as table level encryption. It allows client application to access which has encryption key. This encryption key is not passed to the SQL Server. This feature is also worked with cloud.
JSON Support
Java Script Object Notation is now supported by SQL Server 2016. Now we are able to interchange JSON data between application and database engine. 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, there is built in with "FOR JSON" and "OPENJSON" functions.
Row Level Security
Row level security enables database engine to restrict access to row data which is based on server login. By using filter predicates that defining inline table value function, restricting rows will be done. This is ensuring by Security policies that filter predicates get executed for each and every SELECT or DELETE operation. With this feature of SQL Server 2016, user need not to take care for restrict data from some login by writing code. This will maintain database engine itself.
PolyBase
Big Data and Hadoop have been introduced for handling large amount 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 adhoc 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 Table are tables that hold the old version of rows within base table. If database has a Temporal Table, SQL engine automatically moves old version of 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 seeingour 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 user who is not authorized to see these data. For example, if we stored mobile number or email address of the user in our table but want to restrict user to see whole mobile number or email address and it might be able to see some part of mobile number or email address. At this time we can use Dynamic Data Masking. By setting up this feature, we need to setup the masking rules so unauthorized logins can only read some part of mobile number or email address and authorized logins can see whole mobile number or email address.
Multiple TempDB Database support at the time of installation
Best practice says it would be good have multiple tempDB files if database server running on multi-core machine. Up to SQL Server 2014, we need to create additional tempDB data files manually once installation 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 gave slower performance recently. Prior to this version we can examine execution plans by using DMVs (dynamic management views) but it only allows us to see the plans which are active in cache. This feature is now enabling us to examine historical execution plans and also save query statistics.
R-integration
SQL Server 2016 is now support in-database advance 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 move over to the Azure SQL database. When we run query on database, it would access active record first (local database) and then historical data from 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, check, unique constraints and parallelism. SQL Server 2014 is support in-memory table up to 256 GB but it now extend to 2 TB.
Summary
SQL Server 2016 comes with many more new feature and enhancement. This version of SQL Server has improved probably each feature of previous version for example column store indexes, new feature of operational analytics, etc.
Read more articles on SQL Server: