SQL Server 2016 Top Features

SQL Server 2016, Azure SQL DB and related cloud technologies, are Microsoft’s implemented vision for a complete data platform that offers everything that has to do with data management, transformation, knowledge extraction from structured, semi-structured and unstructured data and beyond. Read on, to learn more about SQL Server 2016 Top Features.

At a recent community event, I talked about SQL Server 2016 and its top features and i can assure you, SQL Server 2016 comes with a rich set of exciting new features as well as many enhancements to features shipped with earlier SQL Server releases such as In-Memory OLTP! The features I consider the most significant in the upcoming release of SQL server are:

Stretch Database

Stretch Database stores historical data in the Microsoft Azure cloud. It runs in the background and provides a seamless access to both local and remote data. Among other, it can be used for archiving processes.

Built-In JSON Support

JSON stands for JavaScript Object Notation and it is widely used for data representation in the data services world. With JSON support, you can format query results as JSON by adding the FOR JSON clause to a SELECT statement in SQL Server. Also, you can converts JSON data to rows and columns by calling the OPENJSON rowset provider function.

In-Memory OLTP Enhancements

The In-Memory OLTP Engine uses non-blocking multi-version optimistic concurrency control that is having multiple version of rows of data loaded in memory thus eliminating both locks and latches. It features Memory-optimized tables (Durable & Non-Durable) and Natively-compiled stored procedures and when they are utilized in a plethora of scenarios, they can offer significant performance boost. In-Memory OLTP was first introduced in SQL Server 2014. In SQL Server 2016 a large number of improvements is provided having as a highlight the following: support for all collations, support for DML triggers in memory-optimized tables and support for more T-SQL constructs in natively-compiled SPs.

Query Store

With Query Store you can get insights on query plan choice and performance. You can also quickly find performance differences caused by changes in query plans.

Temporal Tables

Temporal Tables is a new type of user table. It was introduced in ISO/ANSI SQL 2011. The main role of temporal tables is to keeps a full history of data changes. This allows easy point in time analysis. In temporal tables, the period of validity for each row is managed by the system (i.e. Database Engine). Some benefits of Temporal Tables are: auditing of all data changes/data forensics, data reconstruction/recovery, trends monitoring over time, etc.

TempDB Enhancements

You can now configure TempDB via SQL Server 2016 installation wizard. For example you can set the number of data files and growth settings, as well set multiple volumes for TempDB database files. Additionally, now all TempDB file will grow at the same time so there is no need any more for the Trace flags 1117 and 1118.

PolyBase

With PolyBase you can access data stored in Hadoop or Azure Blob Storage with T-SQL Statements. As of that, you can query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for Data Warehousing workloads and it is intended for analytical query scenarios.

Always Encrypted

This feature is designed to protect sensitive data. It allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. To this end, through a transparent encryption for the client applications, data is not visible to the DBA. In order for the client to be able to decrypt the data you just need to install an Always Encrypted-enabled driver installed on the client computer.

Dynamic Data Masking

Dynamic Data Masking in another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masks: Default, Email, Custom String and Random. A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department.

Row Level Security

Row Level Security controls access to rows in a table based on the characteristics of the user executing a query. An example is having salesmen see only sales they did and not all the sales in a table. The access restriction logic is located in the database tier and access restrictions are applied always and cannot be skipped.

SQL Server R Services

With SQL Server’s support for R you can call the R language runtime through T-SQL and thus uncover new insights and create predictions on top of your data.

In subsequent articles I will be presenting each one of the above features with comprehensive description and many demos!

• MS Docs Article: What’s New in SQL Server 2016, November Update