SQL Server 2016 New Features!

During the Build and Ignite conferences, Microsoft has given everybody a peek into the new features and capabilities that will come with next year’s release of SQL Server 2016. Now I know that most people are still catching up to SQL Server 2014, but all of us data geeks can’t help but get excited every time we hear about the new stuff! Plus, if you’re currently thinking about upgrading, knowing that 2016 is coming and what it can do is a good reason to start discussing, planning and budgeting for upgrades in your organization.

So, I’ve summarized all the new announcements from the past couple of weeks in one spot for quick reference. Also note that SQL Server 2016 will get a preview this Summer and a release next year so many more things might still be announced and some of the capabilities might change, I will update this post in those cases.

These are divided by topics with the main changes for each one, let’s check them out!

Improvements on AlwaysOn Availability Groups:

– Standard Edition will come with AGs support with one db per group synchronous or asynchronous, not readable (HA/DR only).
– Improved log throughput.
– 3 sync replicas, up from 2 in SQL 2014.
– Listener will be able to do round-robin load balancing of read-only requests on multiple secondaries.
– Better policies for failover, for example, looking at individual database health.
– Microsoft DTC support.

Improvements on Columnstore Indexes:

– Parallel insert.
– Nonclustered B-tree indexes on top of the columnstore.
– Read/write nonclustered columnstore on top of a regular clustered index.
– In-memory columnstore on in-memory oltp table.
– More T-SQL constructs run in batch mode.
– More predicates and aggregates pushed down to the storage engine.
– REORGANIZE will remove deleted rows and merge small rowgroups, online.
– New DMVs, XEvents and Perfmon counters.
– Support for RCSI or Snapshot isolation.
– Fully readable on an Availability Groups secondary.

Improvements on In-Memory OLTP
– In-memory OLTP will support Foreign Keys between in-memory tables.
– Size limit increased from 256GB to 2TB.
– Storage file garbage collector tuned to be faster by decoupling the GC from Filestream.
– Transparent Database Encryption (TDE) is supported now for in-memory tables.
– Check and Unique constraints added as well.
– Multi-threaded checkpoint for in-memory (one per container).
– ALTER procedure and sp_recompile supported (fully online).
– ALTER TABLE for schema and index changes (offline). Requires 2x memory.
– Parallel plans are now possible on in-memory tables.
– Native compilation support for: outer joins, OR, NOT, UNION (ALL),DISTINCT, Subqueries (IN, NOT IN, EXISTS, NOT EXISTS).
– Natively compiled stored procedures can be nested.
– Ability to create natively compiled UDFs.
– Trigger support added to in-memory tables.
– Improvements on the migration wizard.
– Wide range of collations supported now.
– LOB support being worked on to add for this release.

Improvements on DBA features:
– Query store: this is a new capability that allows tracking changes of execution plans, comparing different plans and easily telling the optimizer what plan to use.
– Truncate at Partition level.
– Instance will have better handling of multiple Temdb files. So far the material on this is not 100% clear on whether this just means better default for tempdb, some kind of screen on the setup that recommends the amount of files or if SQL Server will be able to automatically add Tempdb files if there’s contention. We’ll have to wait and see.

Improvements on Azure integrations
– Snapshot backup to Azure storage when running as a VM on Azure and with files directly on blob storage.
– Stretch a SQL Server table to Azure. This will allow to have a table that expands from on premises SQL Server to Azure (for example, keep your archive in the cloud and hot data on premises).

Improvements on Dev features:
– Native JSON support.
– Temporal database: transparent and built-in functionality to track historical changes in the data (for example, an address or name change).
– SQL Dev integrated back into one experience: Sql Server Data Tools for Visual Studio 2015 will have both relational and BI database projects.

Improvements on Security:
– AlwaysEncrypted: columns are encrypted using keys on the application driver side so the data on the database is always encrypted and transparent for the application users.
– Dynamic Data Masking: already on Azure SQL DB V12, this allows establishing policies to mask sensitive column values to specific users or roles.
– Row Level security: also available on Azure SQL DB V12, this allows establishing policies that filter our specific rows based on the user trying to access the data.

Integration with Hadoop
– Polybase included in SQL Server (previously PDW/APS only) to query HDFS with T-SQL and integrate with SQL Server.
– You’ll be able to stand up a cluster of SQL Server 2016 instances, one head node and one or more compute nodes and do parallel processing with Hadoop through Polybase.
– Note this is NOT “building your own PDW”, the SQL Server compute nodes don’t have local SQL Server data that can be referenced. “More of a business decision than a technical decision” according to Dr. Dewitt’s session at Ignite.

Improvements on BI
– Datazen is available now for any SQL 2008+ Enterprise + SA license holder, should be further integrated with the BI stack by release time.
– SSRS is getting a makeover to have a modern look, mobile experience,all browser compatibility, paramater layout customization, new visualizations.
– R modules will be able to execute in SQL Server, moving the analytics code close to the data.
– SSIS designer support for previous versions.
– PowerQuery support in SSIS.
– PowerBI Q&A through the SSAS on-premises data connector.
– Pin SSRS reports to PowerBI dashboards.
– Many to Many relationships on SSAS Tabular.
– Built-in time intelligence on SSAS Tabular models, new DAX functions, parallel partition processing.
– New scripting language for Tabular model projects.
– Netezza as a native data source for SSAS.
– DBCC instruction support for multidimensional for maintenance related tasks.
– Tight integration and improvements in relation to Sharepoint vNext.

Phew, that’s a lot of stuff! What do you guys think about these? Will any of them be a big game changer for you? Did we miss something (there’s a lot!)? Let us know in the comments!

Warner Chaves
Author: Warner Chaves
SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.


  • Avatar
    Priyanka Chouhan

    Very simple and good way to describe the features.

  • Avatar
    Brian Howard

    Will TDE make it into the Standard version of SQL this time?

    • Warner Chaves
      Warner Chaves

      Hi Brian, no word has made it out yet on whether there are major changes of features moving to Standard other than what I posted above. If and when it’s made official I will update the post.

      Interestingly, all tiers of Azure SQL Database offer TDE encryption…

Leave a Reply