SQL Server 2016 – What Are The New Features?

SQL Server 2016 will be made available to purchase by the public on 1st June 2016 and if we compare this schedule with Microsoft’s plans of a major release every 2 years, this one comes 26 months after SQL Server 2014 which arrived on 1st April 2014.

So will the list of new features in SQL Server 2016 persuade businesses to upgrade?

This list is by no means complete and so I think I’ll be updating this list as I discover them.

As SQL Server will come in different versions, typically the more businesses are prepared to pay, the richer the feature set available.

• Always Encrypted – as it says on the tin, the data is always encrypted. This means that access to encrypted data will only be available to the application using SQL Server. The client application has the encryption key and this is never passed to SQL Server.

• Query Store – in older versions of SQL Server, query plans can be examined using DMV’s which interrogate the plan cache. Once the plans exit the cache, they are no longer visible for comparison anywhere. The query store provides a way to store older versions of execution plans allowing comparisons between old and new to be made. A nice one for those wanting to check performance of plans over time.

• Live Query Statistics – allows the execution plan to be viewed live as the query is executing – nice!

• Dynamic Data Masking – when switched on, this feature controls what data is masked from view to users who are not allowed to view it. For example confidential columns may be obscured from view to a group of unauthorized users but the other columns would be visible. Parts of columns may also be obscured from view.

• Multiple TempDB Files – adding additional tempdb files is not a new feature as such and has been good practice to implement on multi-core machines. What has been added is the ability to do this upon installation of SQL Server.

• Polybase – allows the querying of Hadoop or Azure blob storage data sets using T-SQL. It’s possible to write queries which join the relational data in SQL Server with the semi-structured data in Hadoop.

• Row Level Security – access to row data can be restricted based on specified SQL logins. This will be done by filter predicates defined inside of inline table value functions. Security policies will ensure that the filters are executed.

• R Support – this feature enables data scientists to run R code directly against the SQL Server. Data will no longer need to be exported separately to facilitate these kind of analytic operations.

• Stretch Database – allows on premise databases to be extended into the cloud. Frequently used data would be stored on premise with older data stored offsite in an Azure database inside the cloud. A question one might ask is what if the on-premise location’s internet link goes down? Only a feature for organizations with good communications redundancy perhaps?

• JSON Support – MySQL’s doing it in the latest 5.7 release, Postgres has been doing it for years, Oracle’s doing it and it’s coming soon in MariaDB. Relational database products are adapting to include support for JSON and SQL Server has joined the party. JSON data can be parsed and stored in relational format. Functions are included which allow for JSON to be queried and output results from queries can also be formatted as JSON.

• Temporal Tables – helps to facilitate automatic row versioning. Every time a row is updated in the base table, a copy of the older version is made inside the temporal table. The temporal table is physical object inside of the database. It is separate to but linked to the base table.

• Backup to Azure – on premise backups can be made to Azure blob storage.

• Managed Backup – this is automated managed backup of your on-premise backups to Azure.

Improvements to existing features in SQL Server

“There is always room for improvement” and existing features have been enriched in places too. What I have been able to ascertain so far is is that there are improvements made to Columnstore Indexes, AlwaysOn and In-Memory OLTP. I’m sure I’ll discover more as I delve deeper.

A worthy release?

It certainly would appear that there are more features than what was included in SQL Server 2014 and if businesses were thinking of upgrading then this version would be well worth considering.

As ever, those decisions will be based around cost and requirements. There will still be businesses running on older versions of SQL Server no longer supported (2005 support finished ended as of April 12th 2016) and they are quite happy to do so because those versions do the job just fine.

For those businesses requiring a supported version, increased performance and some or all of the newer features but have been delaying upgrading, this release may be enough to persuade them to now move forward.