Minette Steynberg

What’s new in SQL Server 2016 summary

June 22, 2016 by

SQL Server 2016 became publically available on the 1st of June 2016. Some might feel that a new SQL Server release every 2 years might be too frequent with many organization still lagging behind by running SQL Server 2012 or earlier.

So what is the fuss all about? Microsoft proudly mentions the following 5 topics as the pinnacle of SQL Server 2016’s offerings.

  • OLTP with increased performance, scalability and high availability

  • Enhanced data security with Always Encrypted

  • Mobile Business intelligence

  • Advanced analytics with R Services

  • Consistency between your database servers and your database as a service (Azure)

So what’s new? In this article I will do a quick summary of the new or improved features which will certainly open up a whole new world of excitement for the data professional.

Faster results

No one likes to wait for query results. We want it to be instantaneous. SQL Server 2016 gets closer than ever to delivering this with significant enhancements to In-memory OLTP, in-memory analytics and improvements to Analysis services.

In-memory OLTP

Memory optimized tables can now have foreign keys, check and unique constraints, triggers (WITH NATIVE_COMPILATION), large object type columns and can use any collation. Statistics for memory-optimized tables are now also auto updated.

A couple of enhancements were also made to indexes from memory-optimized tables which now allows Unique indexes, columns using any collation and Nullable index key columns.

In-memory analytics

Column store indexes can be added to memory-optimized OLTP tables which allows real time analytics to be performed on your OLTP database without the risk of resource contention between your OLTP workload and your analytics workload. In order to minimize the overhead caused by maintaining clustered column store indexes, filtered column store indexes can be used.

Analysis Services

Analysis Services continues to offer 2 modes of data storage both of which have been optimized for performance in 2016.

Multi-dimensional mode

The multi-dimensional engine has been improved to return data faster, these enhancements do not need any configuration in order to be taken advantage of but will only really be evident if you compare the baseline of your current server to that of SQL Server Analysis Services 2016.

Tabular mode

Tabular mode which is based on the same technology as column store indexes has been improved by enhancements to DAX, storage engine caching and T-SQL generation of DirectQuery mode.

Database engine enhancements


TempDB has been improved with performance in mind. You no longer have to determine how many TempDB files you have to add for yourself. Instead SQL server 2016 decides on a default to use by taking into account the number of logical processors on the server. This circumvents the latch contention issue caused by having only 1 TempDB file, (as was the default in previous versions). This is of course still configurable, but now you can do this during your SQL Server install.

Trace flags for improved performance

Trace flags 1117 and 1118 which had to be enabled specifically in the past, has now been included in the database engine and has such no longer need to be activated manually.

The Query Store

The Query Store is a completely new feature which will help DBA’s to troubleshoot performance issues resulting from changes in the query plan. Historical data is captured to allow for comparisons to be made between queries, plans and statistics. They Query Store is not enabled by default, so in order to use it you can enable it with this command:

Once enabled the following 4 dashboards are immediately available:

  • Regressed Queries

  • Overall Resource Consumption

  • Top Resource Consuming Queries

  • Tracked Queries

All of which will allow you to see the data captured in the Query Store.

Stretch Database

The purpose of the new Stretch Database feature is to essentially split hot, warm and cold data and seamlessly move the warm data to Azure, thereby improving the performance of your hot data, reduces your on-site hardware requirements while at the same time allowing you to keep all of your data indefinitely.

Data access still occurs through your on-site database, and no changes to applications are required and the security model of the tables involved remains the same.

Stretch Database supports Transparent Data Encryption which can be enabled to address any concerns about the security of the data in the remote data center as well as data in transit between the local and the remote data centers.

Increased security

There are 3 new security features introduces in SQL Server 2016.

Always Encrypted

With Always Encrypted, the data is, well… always encrypted. The encryption takes place on the client side when it is written, and requires a special driver and an encryption key in order to be able to read the encrypted data.

Row Level Security

Row level security allows you to restrict the rows that a user is able to see, allowing them only to see rows which are relevant to their function. Row level security is implemented by creating inline table functions. This allows a lot of flexibility in terms of what criteria could be used to determine a user’s access but could also have a performance impact if not handled with care.

Dynamic Data Masking

SQL Server 2016 contains 4 new masking functions which can prevent users from viewing sensitive data unless they have been explicitly granted permission to view it. The masking is applied to the query results and as such do not require any changes to existing applications. A DBA running a query in SSMS for instance will also only see the obfuscated data.

Better High Availability

AlwaysOn availability groups

Although not new to SQL Server 2016 there has been some significant improvements to availability groups in this version.

  • AlwaysOn Basic Availability Groups

    This is similar to database mirroring and will be available in the SQL Server 2016 standard. Just like mirroring this is configured on the database level, and only one secondary is allowed which is not readable.

  • Group Managed Service Accounts

    The purpose of this is to facilitate the management of service accounts in large organizations. This has actually been around since SQL 2012, but in SQL Server 2016 gMSAs can be used with availability groups and failover clusters.

  • Database failure failover

    In previous versions a failover of an Availability group was only triggered if the sp_server_diagnostics procedure returned and error, the SQL Server service has stopped or if the instance is not responsive. I did not take into account the state of any database. Enabling the Database Level Health Detection option allows for the availability group to also trigger a failover in case a database becomes inaccessible for whatever reason.

  • Distributed Transaction Coordinator (DTC) support

    DTC is now supported for AlwaysOn Availability groups provided that you are running either Windows 2016 or Windows 2012 R2 with KB3090973 and that your Availability Groups have been created with the WITH DTC_SUPPORT = PER_DB option.

  • Load balancing for readable secondary replicas

    In previous version read traffic always got directed to the first readable replica. In SQL Server 2016 multiple replicas can be specified in the READ_ONLY_ROUTING_LIST and grouped for load balancing using parenthesis. Such as :

    In this instance SVR1, SVR2 and SVR3 will be accessed as a load balanced set and SVR4 will only be accessed if none of the servers in the load balanced set is accessible.

  • Multiple automatic failover targets

    In SQL Server 2016 you can now specify up to 3 automatic failover targets , as long as one of the targets are in sync failover can occur automatically with no data loss.

  • Improved log transport performance

    The log-capture step on the primary server and the redo step on the secondary used to be single threaded in previous versions of SQL. In SQL Server 2016 these two steps are multi-threaded which significantly increases performance.

    Seamless Data Integration

    SQL Server 2016 introduces some useful features which facilitates access to different types of data, including integration between relational data, unstructured and semi-structured data.

    Temporal data

    SQL Server 2016 introduces temporal tables for keeping track of the state of data at any specific point in time. Temporal tables actually consists out of 2 tables. The current or temporal table and the historical table which uses page compression for space considerations. As changes occur on the temporal table a copy of the data is stored in the historical table.



    The addition JavaScript Object Notation support in SQL Server 2016 allows you to import and export data to and from JSON format into your relational database. This means that both relational and non-relational data structures can live together in one environment.


    SQL Server 2016 improves the 2014 implementation of PolyBase by now allowing you to directly query data in Hadoop or Azure blob storage, and have the computation performed on Hadoop for optimized performance. You can use transact SQL to communicate between the non-relational databases such as Hadoop or Azure blob storage and the relational SQL Server database.

    Reporting Services Enhancements

    There are 3 key things to mentions when it comes to the enhancements to SQL Server Reporting Services. 2016 has a better development environment with more data visualizations and new report content types. Mobile access to reports are now possible and a new development environment to support those mobile reports.

    Deeper Analysis

    A vast array of improvements have been made to improve the analytics capabilities of SQL Server 2016.


    SQL Server 2016 no has R Services which allows statisticians, analysts and scientists to execute R code directly on their SQL Server database. The Enterprise Edition of R Services support high speed computations by using multi processors, multicores and multithreading.

    Tabular improvements

    • Access data using DirectQuery mode

      More data sources are now supported for Direct Query. These are:
      SQL Server 2008 or later, Azure, Analytics Platoform System, Oracle 9i and higher and Teradata V2R6, V2

    • All data is no longer required when modeling with a DirectQuery source

      You now have the option to choose to model with no data, a subset of data or all data as in previous version.

    • Calculated Tables

      Calculated tables can be built with DAX in models that are not using DirectQuery mode, which will allow you to combine columns from different tables, apply logic to filter the data or apply calculations.

    • Bi-directional cross-filtering

      You are now able to choose the filter direction of your table relationships. Previously filters could only be applied in one direction which required complex DAX queries in order to avoid duplicates. A relationship can now be filtered in two ways, either single like before or both directions with.

    • Enhancements to DAX

      The formula bar has been improved to facilitate the writing of DAX formulas. Many new DAX functions have been added and variables can now be used in DAX expressions.

      SQL Server Azure

      Initially Azure only offered a subset of the features which are available in a regular SQL Server database engine. Most of the limitations of the initial Azure SQL Database has now been overcome leaving SQL Database close to par with on-premises servers. Size limitations has been increased, additional security has been added and many database features included. All Azure SQL Databases come standard with high availability, and a minimum of 3 replicas exist which can be promoted to primary in the event of a hardware failure.

      SQL Database Security

      Azure security has been enhanced built to be trustworthy with advanced strategies and policies to ensure that only authorized users are able to access your data. The policies are regularly audited by accredited third-parties.

      SQL Database Auditing

      Auditing is included in Azure and logs are written to Azure storage. The following events can be audited.

      • T-SQL whether plain or parametrized

      • Schema modifications

      • Data modifications

      • Stored procedures

      • Logins

      • Transaction management

      Data Protection

      SQL Database has various features implemented to help protect your data, some I’ve already mentioned in the on-site SQL Server 2016 version. These include:

      • Transparent Data Encryption

        TDE takes place on file level. The data in the tables are not encrypted. This prevents unauthorized use of database files and backups, but does not prevent users who have access to the data from viewing it.

      • Cell Level Encryption

        The purpose of CLE is to hide certain sensitive data such as social security numbers by encrypting a column of data.

      • Always Encrypted

      • Row level security

      • Dynamic Data Masking

      Elastic databases

      The elastic database feature is a cost saving mechanism to prevent over provisioning of resources which is common if resources are estimated on a per database basis. Instead Azure allows you to group databases into an Elastic Database Pool and provision resources for the pool instead of for each database individually. When databases are in a pool they can automatically adjust to utilize the amount of eDTU (Database Transaction Units) as required. Sometimes some databases will use less and sometime some databases will use more. So any surplus can be used by other databases in the pool when they encounter a heavier load.

      Azure Data Warehouse

      Azure data warehouse is basically the APS (Analytics Platform System) in the cloud. The aim is to provide a massively parallel computing capabilities to everyone. The SQL Data Warehouse is targeted at databases which encompass at least a Terabyte of data. It includes quite a few optimizations to increase the performance of data warehouse queries, such as a distributed query optimizer and clustered column store indexes.

      Wrapping it up

      This was a quick summary of the new features available in SQL Server 2016 and Azure. Please check out the references below for more in-depth details of each topic.

      Minette Steynberg