New Features in SQL Server 2016:

1. Query Store
2. Polybase
3. Dynamic Data Masking(DDM)
4. JSON Support
5. Stretch Database
6. Multiple TempDB Database Files
7. Row Level Security
8. R Comes to SQL Server
9. Temporal Table
10. Always Encrypted
11. In-Memory Enhancements

1: Query Store

The Query optimizer is one of the major problem faced by many organizations while upgrading versions of SQL Server.

This results in performance degradation. The Query Store feature allows administrators or developers to identify queries that take longer time for execution and replace them from other substitute queries.

This special feature is configured at the individual database level in SQL Server.

2: PolyBase

Polybase is an SQL Server connector to Hadoop or SQL Azure blob storage to its data warehouse appliance Analytics Platform System that has been introduced by Microsoft in 2015.

Now, it has been incorporated into its regular on-premises product of Microsoft. This feature is beneficial in dealing with a lot of large text files while processing data.

3: Dynamic Data Masking(DDM)

Dynamic Data Masking. Especially in the Business Intelligence World or when it comes to reporting or data analytics you often need to anonymize data and don’t report on personal information. This new feature helps here.

Now, it has been incorporated into its regular on-premises product of Microsoft. This feature is beneficial in dealing with a lot of large text files while processing data.

4: JSON Support

SQL Server 2016 also supports the lingua franca of Web applications: Java Script Object Notation (JSON).It provides the ability to quickly move JSON data into tables.

By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format.

5: Stretch Database

“Stretch Database” is a hybrid feature introduced in SQL server in an attempt to reduce the storage and processing costs. A Stretch Database feature provides you a method to stretch the storage of your On-Premise database to Azure SQL Database

This feature works on the concept of moving, a part of your tables into an Azure SQL Database in the cloud in a secure manner. This helps the query optimizer in balancing the workload on your server and Azure.

Microsoft only charges for the SQL Database in Azure when it is used for queries.

6: Multiple TempDB Database Files

It has been a best practice for a while to have more than one TempDB data file if you are running on a multi-core machine.

Up through SQL Server 2014, you always had to manually add the additional TempDB data files after you installed SQL Server.

With SQL Server 2016 you can now configure the number of TempDB files you need while you are installing SQL Server.

Having this new feature means you will no longer need to manually add additional TempDB files after installing SQL Server.

7: Row Level Security

The row-level security (RLS) feature restricts which users can view what data in a table, based on a function. This feature is useful in multi-tenant environments where the user may want to limit data access based on customer ID.

Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation.

8: R Comes to SQL Server

Microsoft’s purchase of Revolution Analytics they are now able to incorporate R to support advance analytics against big data right inside of SQL Server.

By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it right inside the SQL Server database engine.

This will eliminate the need to export your SQL server data in order to perform R processing against it. This new feature brings R processing closer to the data.

9: Temporal Table

A temporal table is table that holds old versions of rows within a base table.

By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.

The temporal table is physically a different table then the base table, but is linked to the base table.

If you have been building or plan to build your own method to managing row versioning then you might want to check out the new temporal tables support in SQL server 2016 before you go forth and build your own row versioning solution.

10: Always Encrypted

SQL Server has supported both column-level encryption, encryption at rest, and encryption in transit. They all need to be configured independently.

Always Encrypted is new functionality so the data stays encrypted in transit, at rest and while it is alive in the database.

11: In-Memory Enhancements

The concept of in-memory tables was introduced in SQL Server 2014. These were specially designed for loading data in high-speed with no locking issues or high-volume session state issues.

However, there were a lot of limitations mainly around constraints and procedures. In SQL Server 2016, this feature is improved to a great extent, supporting foreign keys, check and unique constraints and parallelism.

Moreover, tables up to 2TB are now supported (up from 256GB). Another benefit of in-memory is column store indexes, which are mostly used for data warehouse workloads.

Although, this feature was introduced in SQL 2012 and has been enhanced in each version consecutively. In 2016, it receives some enhancements around sorting and better support with AlwaysOn Availability Groups.