New Features that You Should Know in SQL Server 2016

Microsoft has been released the latest version of SQL Server 2016. Here’s a rundown of our most favourite new features in SQL Server 2016.


SQL Server 2016 New Features – In-Memory Columnstore Indexes & Enhancements

In-memory OLTP was introduced in SQL Server 2014.  SQL Server 2016 adds another twist: Columnstore indexes on in-memory tables! 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). The enhancement in SQL Server 2016 is vastly improved over what was possible in SQL Server 2014, adding the support for foreign keys, unique constraints, and parallelism. Also tables are supported with sizes up to 2TB, which is significantly larger than the old 256GB limit is SQL Server 2014.

SQL Server 2016 New Features – Impact of Cloud-First on SQL Design

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

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.

SQL Server 2016 New Features – 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. 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’s a 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  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.

SQL Server 2016 New Features – Polybase

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. This feature will benefit you if your regular data processing involves dealing with a lot of large text files, they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables. A common scenario where you might use this would be an extract, transform and load (ETL) process, where you were taking a subset of the text file to load into your database.

SQL Server 2016 New Features – JSON Support

In addition to supporting direct querying to Hadoop, SQL Server 2016 adds support for the lingua franca of Web applications: Java Script Object Notation (JSON). Several other large databases have added this support in recent years as the trend towards Web APIs using JSON has increased. The way this is implemented in SQL 2016 is very similar to the way XML support is built in with FOR JSON and OPENJSON, providing the ability to quickly move JSON data into tables.

SQL Server 2016 New Features – Row Level Security

A feature that SQL Server has lacked natively is the ability to provide row-level security. This feature restricts which users can view row data in a table, based on a function. SQL Server 2016 introduces this feature, which doesn’t include updates and inserts but it is a great first step. The implementation of RLS in SQL 2016 still has it limits (updates and inserts are not covered), but it is good start on a much-needed feature.

You can find out more about the upcoming SQL Server 2016 features at the SQL Server 2016 Preview page and the SQL Server Blog.

ASP.NET Hosting with SQL Server 2016 from

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. Looking for ASP.NET hosting which completed by the latest SQL server 2016 technology? As the most popular ASP.NET hosting provider that people looking to host for the first time in Europe, We can provide ASP.NET hosting with unlimited domain, unlimited bandwidth, unlimited disk space and the latest SQL Server 2016 technology which are sure to be high on many people’s priority list. In addition, Microsoft Golden Partner, which means they are the first one to know the latest Microsoft technology and test Microsoft product before being released to the public. Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and many top European countries.