• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2016 – What Are The New Features?

May 17, 2016 by Andy Hayes Leave a Comment

SQL Server 2016 New FeaturesSQL 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.

Filed Under: All Articles, SQL Server News Tagged With: sql server, sql server 2016

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©