SQL Server 2016

Here’s a rundown of my favourite new features in SQL Server 2016, largely inspired by the SQL Server Evolution video.

Impact of Cloud-First on SQL Design

This is a really nice illustration of the consequences of Cloud-First for Microsoft products.

4150.Microsoft-Mobile-First-Cloud-First[1]SQL has been basically flipped around as a product.  When SQL Azure was introduced years ago, it was a version of SQL Server running in the cloud.  Nowadays, SQL Azure drives the development of the SQL Server product.

Being cloud-first allows Microsoft to iterate much faster on different product feature in preview mode, gather a tone of feedback, thanks to the scale of Azure and deploy it (when the development cycles are over) globally very quickly.  That changes the entire dynamic of product development.

The nice thing is that it actually improves the SQL Server product:  when a new version of SQL Server comes in, e.g. 2016 right now, the features have been explored by an order of magnitude greater user base than in beta-test in the old world.

In-memory Columnstore indexes

In-memory OLTP was introduced in SQL Server 2014.  SQL Server 2016 adds another twist:  Columnstore indexes on in-memory tables!

IC709594[1]You can now have an high throughput table being fully in-memory and also have it optimized for analytics (columnstore indexes).

This unlocks scenarios such as real-time analytics (with no ETL to Data Warehouses)

Always Encrypted

Transparent Data Encryption (TDE, only recently added to Azure SQL) encrypts the data on disk.  This addresses mainly the physical compromising of data:  somebody steals your hard drive.

encryption[1]Not a bad start as often data center hard drives still get recycled and you’ll see headlines of data leaks caused by hard drives found with sensitive data on it once in a while.

Now what Always Encrypted brings to the table is the ability to encrypt data in motion, i.e. while you are reading it…  and it is column based so you can very selectively choose what gets encrypted.

With this an SQL Database administrator will only see rubbish in your encrypted columns.  Same thing for somebody who would eavesdrop on the wire.

The cryptographic keys aren’t stored in SQL Database either but on the client-side (Azure Vault anyone?) which means that even if your entire database gets stolen, the thief won’t be able to read your encrypted data.

…  it also mean you’ll have to be freaking careful on how you manage those keys otherwise you’ll end up with encrypted data nobody can read (did I mention Azure Vault?)


Polybase was introduced in SQL Server Parallel Data Warehouse (PDW).

It extends the reach of TSQL queries beyond SQL Server tables to unstructured data sources such as Hadoop.

This will now be part of SQL Server 2016.

Run ‘R’ models in SQL

200px-R_logo.svg[1]Microsoft recent acquisition of Revolution Analytics didn’t take long to have impact.

We will be able to run ‘R’ analytics model right into SQL Server 2016.  This brings the power of R Data Analysis right so much closer to the data!

Advanced Analytics

Power BI is of course at the center of this but also a complete revamp of Reporting Service.

Stretching SQL Server to Azure

A hybrid-cloud approach to SQL Azure:  put your cold data (typically historical) in the cloud storage but keep your database on-premise.

I’m talking about data within the same table being both on-premise and in the cloud!

Quite easy to setup, this feature has potential to be a really nice introduction to the cloud for many organization.  The high value scenario is to drop the storage cost for on-premise application having huge database with most of the data being historical, i.e. cold (rarely accessed).

It is all configuration based, hence not requiring any changes in the consuming applications.

Wrapping up

SQL Server 2016 proves that SQL product line is alive and kicking with very valuable features for modern scenario, be it Big Data, Advanced Analytics or Hybrid Cloud computing.

You can try SQL Server 2016 using the VM template SQL Server 2016 CTP2 Evaluation on Windows Server 2012 R2 in the Marketplace as of this date (22/07/2015).

If you want more details, so far, the best source is that SQL Server Evolution video which is well done.

Leave a comment