In-Depth

What Makes SQL Server 2016 a Game-Changer

Polybase, Query Store, dynamic data masking, row-level security, and temporal data JSON support -- just some of the new features that veteran SQL Server Live! presenter Leonard Lobel covers in this exclusive Q&A with Lafe Low.

• By Lafe Low
• 11/08/2016

There's a lot going on in the SQL Server world. The list of new features in the recent SQL Server 2016 release includes game changers like Polybase, Query Store, dynamic data masking, row-level security, and temporal data JSON support. It opens up a range of new possibilities for developers.

We caught up with veteran SQL Server Live! presenter Leonard Lobel to get some of his thoughts on the new SQL Server 2016 and what he plans to cover at the upcoming SQL Server Live! conference as part of Live! 360 in Orlando, FL from December 5-9, 2016.

Q: SQL Server 2016 has a lot to offer, but if you had to pick one thing, what do you think is the number one most important new feature?
A:That is a really tough question, because SQL Server 2016 has so many great new features and they're all important. If I had to pick one though, it would have to be temporal. I mean, who wouldn't like to be a time traveler?

And temporal data lets you do just that. When you designate a table as a temporal table (technically referred to as a "system-versioned" table), SQL Server automatically tracks updates and deletes made to that table and records those changes to a history table. Then the query engine integrates with the history table and gives you this almost magical experience of time travel. You can run a query against the table as it appeared at any past point in time. So data you overwrite or blow away with an UPDATE or DELETE statement is never really lost. It's always available simply by telling your otherwise ordinary query to travel back in time when looking at the table.

Think of all the great uses for this feature. Being able to query data as it changes over time yields tremendous business value, where temporal makes it easy to run all sorts of trend analysis. Temporal tables also give you an inherent auditing solution, when you need to track what has changed and when it changed (although it won't record who made the change).

What about that heart-dropping moment after an UPDATE or DELETE that you didn't really mean? Instead of a panic and scramble to find that backup and restore it, you can much more easily recover from your accident by accessing the "lost" data from the temporal history table.

What do you think is the top new security related feature?
There are three major new security features in SQL 2016: Dynamic Data Masking (DDM), Row-Level Security (RLS), and Always Encrypted (AE). Of these, AE is probably the most compelling because it eliminates a major block for people who have been reluctant to move their encrypted data (and virtual networks) to the cloud. While previous versions of SQL Server do support encryption, the older techniques only encrypt data at rest, not in flight. Furthermore, the very keys and certificates used to decrypt encrypted data are themselves contained in the database. As a result, there's no separation between those who own the data (the customer) and those who host it (the cloud provider, Azure in the case of Microsoft).

AE changes all that. AE means data is always encrypted. It is never decrypted in flight, nor is it ever decrypted in any location other than the client. AE provides client-side encryption and decryption based on client-side keys, so that data is always encrypted even as it traverses the network. The database itself is incapable of decrypting the data it stores because it has been encrypted on the client. Thus, customers can now enjoy the security of encrypted data in flight that uses client-side keys, and finally move their database to the cloud with unprecedented confidence.

What is Polybase and why should we care?
We're living in a world of big data. Massive amounts of information are being stored in these so-called No-SQL stores, such as Hadoop and Azure Blob Storage.

In SQL Server 2016, Polybase lets you integrate with several flavors of Hadoop, including Hortonworks HDP on Windows or Linux, Cloudera CDH on Linux, and HDInsight on Azure. Hadoop stores its data on HDFS, the Hadoop Distributed File System. Polybase lets you define external tables in SQL Server that map to flat files stored on HDFS. You can then write T-SQL queries that seamlessly retrieve data from Hadoop, and automatically transform the non-relational data stored on HDFS into the relational schema defined by your external tables.

If you're an experienced SQL Server developer with little to no experience with Hadoop, Polybase dramatically lowers the bar to entry, because you can quickly and easily query (and even join on) non-relational data stored in Hadoop using familiar T-SQL, without having to learn a multitude of other big data technologies.

What is your personal favorite feature in SQL Server 2016?
Again given the multitude of awesome new features in the 2016 release, a personal favorite is a tough call, but this would have to be the new built-in JSON support. Like many folks, I work with a lot of unstructured data. I frequently need a hybrid solution to efficiently integrate unstructured data with structured relational data in SQL Server. In the past, much of this unstructured data has been XML, and the rich XML support available in SQL Server for the past decade goes a long way in marrying the world of unstructured XML with structured relational data.

JSON is the new XML, and SQL Server 2016 provides JSON support very similar to what's possible with XML. You can shred, store, query, generate, and manipulate JSON documents in just about any way that you need to in SQL Server 2016.

For more on Live!360, go here.