Introduction to New SQL Server 2016 Features

Recently Microsoft has made an announcement about the upcoming release of SQL Server 2016. There has been great enthusiasm about features that will be released with the new SQL Server 2016 and the enhancements that are made to the new SQL Server 2016. Below are list of some of the important and useful features introduced with SQL Server 2016 or enhancements made to existing features in SQL Server 2016.
ColumnStore Index – Below are the new enhancements made to the ColumnStore Index in SQL Server 2016. This feature was first introduced in SQL Server 2012, and few enhancements were made in SQL Server 2014 and some great new enhancements are made in SQL Server 2016 as mentioned below.
– Updatable NonClustered ColumnStore Index
– ColumnStore Index on In-Memory Tables
– Single-Thread Vs. Multi-Thread Batch Execution
– Snapshot and Read-Commited Snapshot Isolation
Specify Column in Table Creation

· AlwaysOn Availability Groups – There have been some very important and useful changes to the AlwaysON feature. This feature was introduced in SQL Server 2012 and some enhancements were made in SQL Server 2014 and now in SQL Server 2016 there have been further enhancements as mentioned below.
o DTC – beginning for SQL Server 2016 Community Technology Preview 2 (CTP2), cross-database transactions are supported for AlwaysOn Availability Groups running on Windows Server 2016 Technical Preview 2.
o Automatic Failover replicas have been increased from 2 replicas to 3 replicas.
o Group Managed Service Account –
o Load Balance of Read-Intent – Beginning with SQL Server 2016 Community Technology Preview 2 (CTP2), you can configure load-balancing across a set of read-only replicas.

References – https://msdn.microsoft.com/en-s/library/hh710054%28v=sql.130%29.aspx#loadbalancing,
https://technet.microsoft.com/library/hh831782.aspxHope this was helpful.

· In-Memory OLTP – This feature was first introduced with SQL Server 2014 and there are many new enhancements introduced with SQL Server 2016 as mentioned below.
– Alter Operation – Alter Table and Alter Procedure
– User-Defined Function
– All Collation and Code Pages
– Throughput Increase
– TDE on In-Memory Database
– Multiple threads to persist memory-optimized tables
– Multi-threaded recovery
– Multi-threaded MERGE Operation
– Stored Procedure Check Tables to go to In-Memory
– More Surface Area on Natively Stored Procedures
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx

· Live Query Statistics – Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another.
Reference – https://msdn.microsoft.com/en-us/library/dn831878%28v=sql.130%29.aspx

· Query Store – Query store is a new feature in that provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
Reference – https://msdn.microsoft.com/en-us/library/dn817826%28v=sql.130%29.aspx
· Temporal Tables – https://msdn.microsoft.com/en-us/library/dn935015%28v=sql.130%29.aspx

· Backup to Microsoft Azure – SQL Server backup to URL using Microsoft Azure Blob storage services now supports using block blobs instead of page blobs.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Managed Backup – In SQL Server 2016 Community Technology Preview 2 (CTP2) SQL Server Managed Backup to Microsoft Azure uses the new block blob storage for backup files. There are also several changes and enhancements to Managed Backup.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Multiple TempDB Files – Setup adds multiple tempdb data files during the installation of a new instance
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Transparent Data Encryption Enhancements – TDE has been enhanced with support for Intel AES-NI hardware acceleration of encryption. This will reduce the CPU overhead of turning on Transparent Data Encryption.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Truncate Table with Partitions – The TRUNCATE TABLE statement now permits the truncation of specified partitions.
Reference – https://msdn.microsoft.com/en-us/library/ms177570%28v=sql.130%29.aspx
· Always Encrypted – With Always Encrypted, SQL Server can perform operations on encrypted data, and best of all the encryption key resides with the application inside the customer’s trusted environment and not on the server.
Reference – https://msdn.microsoft.com/en-us/library/mt163865%28v=sql.130%29.aspx
· Row-Level Security – Row level security introduces predicate based access control. It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata or any other criteria the administrator determines as appropriate.
Reference – https://msdn.microsoft.com/en-us/library/dn765131%28v=sql.130%29.aspx
· Dynamic Data Masking – Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.
Reference – https://msdn.microsoft.com/en-us/library/mt130841%28v=sql.130%29.aspx
· Stretch Database – Stretch Database is a new feature in SQL Server 2016 Community Technology Preview 2 (CTP2) that leverage’s resources in Windows Azure to store and query archival data.
Reference – https://msdn.microsoft.com/en-us/library/dn935011%28v=sql.130%29.aspx

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings