SQL Server 2016 was released on June 1st 2016, and, as with all new releases of SQL Server, there was a plethora of improvements and new features. Below we outline a handful of these new features that have got us excited.
What’s new about SQL Server 2016?
SQL Server 2016 is faster
The developers of SQL Server 2016 named their blog series “It Just Runs Faster”. This isn’t just hubris, it is justified thanks to dozens of performance improvements. For example, DBCC, SQL Server’s internal consistency checking utility, now runs 7 times faster. To demonstrate this, there was a collaboration with Intel with a 100TB data warehouse, a single server with four Xeon E7 processors, and SQL Server 2016. It took just 5.3 seconds to run a complex query on the 100TB database.
SQL Server 2016 is more secure
There is a new Always Encrypted option available at the column level. This will encrypt and decrypt data within client applications and never reveal the encryption keys to SQL Server. The downsides to this is that the stored data takes up more memory, and the decryption on the client could cause a performance hit, so the developer will need to be selective around the columns to which this is applied.
Another new feature in this area is Row-level security. This means that SQL queries will only return rows relevant to the user. For example, if a head of department runs a query on an employee table, then only employees in that department will be returned, but the same query run by the head of HR would return all employees.
In a similar vein, Dynamic Data Masking will obfuscate part of a value. A classic example of this is credit card numbers. When a user runs a query for a credit card number, then SQL Server can mask the number in the results so that only the last 4 digits are returned (“XXXX-XXXX-XXXX-1234”). In order to see the whole credit card number, the user would need the UNMASK permission.
SQL Server 2016 has advanced analytics
In January 2015, Microsoft acquired Revolution Analytics, a statistical software company. The result of this is that R Services, which provides analysis in real time and at scale, is now built in to SQL Server 2016. This includes the ScaleR library which provides: data import; sorting, merging and splitting; statistical functions and cross tabulation; data visualization; modelling algorithms; and decision trees. All this can be called directly within stored procedures.
Temporal Tables provide built-in support for storing data for a table at any point in the past. Whenever a record is updated or deleted, a copy of the data is created in a matching ‘history table’. As a Doctor Who fan, I appreciate the implication of time travel, but really it is an easy way to create audit tables. This feature facilitates data forensics, reconstructing the state of the data at some past point, calculating trends, or recovering from accidental data changes.
SQL Server 2016 also provides the ability to combine relational and non-relational data in the same query using the new feature PolyBase. This is a query engine that can query data stored in Hadoop and Azure blob storage, import data from and export data to Hadoop and Azure blob storage, and also integrate with BI tools which are compatible with SQL Server.
SQL Server 2016 offers all the benefits of Azure Cloud
As well as providing Azure SQL databases and Azure VMs with SQL Server pre-installed, SQL Server 2016 introduces the concept of Stretch Databases. This allows the developer to specify either a table for cold data or a filter function to identify cold data within a table, and SQL Server 2016 will then start to migrate that data to Azure. The benefits of this are that it saves on the cost of having to scale up on-premises storage, the data can still be queried, and Always Encrypted data is preserved. There are limitations to this as well: UNIQUE constraints and primary keys are not enforced on migrated data; queries can’t update or delete migrated data; and filters are limited to absolute dates, so for example it can’t migrate records “older than one year”. Even still, this is a valuable tool for archiving data.
Summary
SQL Server has been the market leader for databases for many years now, and with this latest release this looks set to continue. Performance has improved, BI has been expanded, new security features are easy to implement, and Microsoft’s push to the cloud continues. Microsoft have made this latest version a compelling proposition.
