Overview of New SQL Server 2016 Developer Features
Every new version of SQL Server is packed with new features, and SQL Server 2016 is no exception. In this blog post, I briefly describe the major new developer focused features introduced in SQL Server 2016, which just launched on June 1. I’ll cover many of these features in greater depth, in upcoming posts.
• Drop If Exists is a small but convenient language enhancement which helps you write neater T-SQL code, because you no longer need to test if an object exists before deleting it.
• SESSION_CONTEXT gives you a dictionary object that maintains its state across the lifetime of the database connection, so it’s a new easy way to share state across stored procedures running on the server, and even to share state between the client and the server.
• With Dynamic Data Masking, or DDM, you can shield sensitive information in your tables from unauthorized users by masking them, and this works purely with permissions, without ever modifying the actual data in the table.
• Row-Level Security, or RLS, lets you hide different rows for different users, based on your own custom criteria. The hidden rows are automatically filtered out of all queries that get issued against the table, and you can also block users from inserting, updating, or deleting rows according to your needs.
• Always Encrypted lets you encrypt data that is never decrypted in any location other than the client. By using client-side certificates to perform client-side encryption and decryption, the data is always encrypted – not just on disk, but in-flight, as it traverses the network.
• With Stretch DB, you can keep using your own data centers and SQL Servers to host and manage your data, but still allow tables you designate for remote data archive to be migrated to the cloud on Azure SQL Database. So you keep your hot data on-premises, but let your cold data stretch out to the cloud, completely transparently.
• Temporal data is an exciting new feature that automatically tracks changes made to a 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, where you can run a query against a table as it appeared at any point in time of the past.
• JSON support. XML support first appeared back in SQL Server 2000, and then got a major boost in 2005 with the native XML data type. Today, 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, and manipulate JSON documents in just about any way you need to, in SQL Server 2016.
• Hekaton improvements – SQL Server 2014 introduced In-Memory OLTP, which many still call by its code name, “Hekaton.” Hekaton can dramatically boost performance by migrating from traditional disk-based tables to newer memory-optimized tables. The technology is compelling, but the initial release in 2014 carried a lot of limitations, and the most egregious ones have been removed in 2016.
• PolyBase – We’re living in a world of big data, where increasingly, massive amounts of information is being stored in large No-SQL stores such as Hadoop and Azure Blob Storage. PolyBase is a new feature in that lets you integrate with both Hadoop and Azure Blob Storage. By defining external tables that map to these No-SQL environments, you can write T-SQL queries that seamlessly retrieves data from them, and can even push portions of the query down to execute on Hadoop as a compute job.
• The new QueryStore feature will cache execution plans, and capture the performance of the same query over time. This is a great tool when you’re trying to troubleshoot the performance of a query that once had a good execution plan, but no longer does because of some environmental change, say some change in table statistics. With query store, you can much more easily identify that change, and make the necessary adjustments to ensure that your SQL Server continues to devise good execution plans.
• R Integration – R is an analytic programming language that has grown very popular over recent years, and now SQL Server 2016 introduces R services. This lets you write code in R and run it right inside the database engine itself. This is a huge win for data scientists who will no longer need to first extract their data out of SQL Server before they can analyze it with R; instead, they can bring their R code right to the data, and let it run there.