SELECT features FROM bumf... What's new in MS SQL Server 2016

Query Store

Query Store lets you track query performance

Query Store is a completely new feature which stores query texts, query execution plan, and performance metrics within the database. It is off by default, but easily enabled in database properties. A simple concept, but one that enables easy monitoring and troubleshooting of performance issues. A dashboard shows which queries consumed the most time, memory or CPU resources. If different plans (execution steps) were used for the same query, you can force future use of the more efficient plan.

Temporal tables: A full history of data changes

Temporal tables, also new in SQL Server 2016, are history tables which record all data changes, complete with the date and time they occurred.

Temporal tables store a full history of data changes

The existence of temporal tables, when enabled, means that you can construct historical queries with expressions like "SELECT ... FOR SYSTEM_TIME BETWEEN ...." This is ideal for auditing data changes, the disadvantage being database bloat. If you need to hang onto the data forever, Microsoft suggests using the Stretch feature (see below) to migrate older data to Azure, or you can delete old data, or partition tables.

SQL Server has always maintained a transaction log, enabling rollback to older versions of the database, but temporal tables are better suited to auditing and historical analysis.

Stretch to the cloud

The Stretch Database feature lets you push cold data into Microsoft's Azure cloud while retaining the ability to query it as before. You create a linked database in Azure, and SQL Server automatically includes both local and remote data in your queries. Entire tables can be migrated to Azure, or if a table has a mix of current and historical data, you can create a function to determine whether a row will be migrated.

The stretching is not seamless. You cannot update or delete migrated rows, and there is what looks like a deal-breaking limitation for active tables: "Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data." If necessary, you can migrate tables back on-premises to work around any issues.

This is one of SQL Server 2016's less convincing features. It is designed to save on-premises storage space, but storage keeps getting cheaper. Microsoft has also constrained the feature so that you cannot use a linked database on your own network, and you cannot use an alternative cloud provider. However it does support Always Encrypted, and Microsoft says that linked databases cannot be accessed other than by system processes, so security looks good.

PolyBase: T-SQL for Big Data

PolyBase lets you query Hadoop data with SQL Server

PolyBase is a query engine designed to bridge the relational world of SQL Server with the large less-structured datasets typically stored in Hadoop and used for analytics. Working with PolyBase means that users can work with familiar SQL tools rather than MapReduce. You can import and export data as well as executing queries.

PolyBase supports HortonWorks or Cloudera Hadoop versions, or you can use it with Azure Blob storage. You can configure PolyBase to push computation down to Hadoop to improve performance. Setting up PolyBase involves configuring both Hadoop and SQL Server, so that the Hadoop data appears as external tables. Performance may be an issue and you can analyse the execution steps to troubleshoot problems.