Microsoft SQL Server 2016 has been one of the hot topics of 2015. While the Microsoft roadmap doesn't show the product's general release until next year, a succession of previews has people talking about the upcoming changes in a new addition of SQL Server.
SQL Server 2016 was announced at Microsoft Ignite in May. The first community technology preview (CTP) was released three weeks later. The most recent updates on SQL Server 2016 are CTP 3.0, released in October, and the newly released CTP 3.1. The CTP releases have given the SQL Server community time to check out what's coming in SQL Server 2016, and fans have already begun to pick out favorite features. In that spirit, here are four of 2015's top features for SQL Server 2016.
In-memory OLTP broadens its reach
In-memory online transaction processing (OLTP) was first introduced in SQL Server 2014, but Microsoft SQL Server 2016 removes some of the limitations from SQL Server 2014. For example, in SQL Server 2014, memory-optimized tables were capped at 256 GB of memory. SQL Server 2016 raises the upper limit on memory-optimized tables to 2 TB. In-memory transactions in SQL Server 2016 are also supposed to run 30 times faster than in SQL Server 2014.
However, possibly the most notable change for in-memory in SQL Server 2016 is that it is now possible to edit memory-optimized tables after creating them. In the previous version of SQL Server, in order to modify a memory-optimized table, users had to drop and then recreate the table. In SQL Server 2016, memory-optimized tables can be edited using T-SQL code.
Security offers encryption option
Microsoft SQL Server 2016 has several new security features: Always Encrypted, dynamic data masking and row-level security. Always Encrypted is probably the most well-known of the groups. It allows users to perform operations on encrypted data, without having to decrypt it first. This is the first SQL Server feature to ensure encryption both in motion and at rest.
Dynamic data masking lets people hide part of their data from general users, while revealing everything to users with the correct privileges. Unlike Always Encrypted, dynamic data masking isn't encryption, since the data isn't masked in the storage device. Instead, it can be one part of a security strategy and is probably most useful for regulatory compliance.
Finally, row-level security began as a new feature for Microsoft Azure V12, but will also be available for SQL Server 2016. It allows the administrator to control who has access to the database at the row level, based on the user executing the query.
Analytics features PolyBase integration
Microsoft SQL Server 2016 fills in some of the gaps in SQL Server's analytics toolkit. While PolyBase has been a part of Microsoft Analytics Platform System long before SQL Server 2016, the upcoming release is the first time PolyBase has been directly integrated into SQL Server. PolyBase is a transparent access layer that connects the SQL Server database engine to sources of unstructured or semistructured data. In other words, PolyBase and SQL Server 2016 allow users to directly connect SQL Server and Hadoop via T-SQL programs.
SQL Server Analysis Services is also getting a long-awaited new feature in SQL Server 2016. CHECKDB is a new command for the database consistency checker that performs a database consistency check for both tabular and multidimensional databases.
Stretch Database tackles storage concerns
Stretch Database is a new feature for Microsoft SQL Server 2016. At Microsoft Ignite, CEO Satya Nadella dubbed the feature as "an infinite database." Its name stems from the idea of an on-premises database that "stretches" into Azure SQL Database, so infrequently used data can be stored there, while more frequently used data is kept on premises. Ideally, Stretch Database will answer the question of whether to delete data from an index. With Stretch Database, instead of having to grow an on-premises database to hold an ever-increasing amount of historical data, the data can be stored more cheaply on the cloud, while remaining searchable with the on-premises database.
These improvements are hardly a comprehensive list of the changes between SQL Server 2014 and SQL Server 2016. However, these are four of the most anticipated features that we'll be seeing when Microsoft SQL Server 2016 is released next year. In the meantime, it's possible to test out all of these new features in the CTP.
Microsoft CEO Satya Nadella announced SQL Server 2016 at Microsoft Ignite 2015
A closer look at improved cloud, security and analytics in SQL Server 2016
Microsoft SQL server 2016 gets an early review from beta testers