SQL Server 2016 Features in CTP2

By: Koen Verbeeck   |   Comments   |   Related: > SQL Server 2016

Problem

At the time of writing, SQL Server 2016 is peeking around the corner. Recently a preview - SQL Server 2016 CTP2 - has been released. Since it doesn’t yet contain all new features of SQL Server 2016, this tip will aim to give an overview of the features present in the CTP2, so you know what to expect.

Solution

You can download the SQL Server 2016 CTP2 preview here. It’s an Evaluation edition, so you are not charged for its use.

A lot of great features have been announced, but not all of them are included in the preview. This tip presents an overview of the new features that made it into CTP2. A broad description of every new feature is out of scope for this tip. Future tips will delve deeper into some of the new features.

SQL Server 2016 Features - Database Engine

This is not an exhaustive list, but rather an overview of the most important new features. A full overview can be found on MSDN.

• Always Encrypted. Ensures data is always encrypted. Encrypted data is stored and manipulated by SQL Server and transparent to the application.
• Enhanced In-Memory OLTP
  • A lot of limitations from SQL Server 2014 have been lifted. The following article gives a good overview: SQL Server 2016: In-Memory OLTP Enhancements.
  • Columnstore indexes can now be created on top of an In-Memory OLTP table, opening the door for real-time operational analytics. More info: Columnstore Indexes for Operational Analytics.
• Polybase. Previously a Parallel Datawarehouse feature only, Polybase has made its way into the standard SQL Server product. With Polybase, you can write SQL like queries on top of Hadoop data. Installing this feature requires Oracle Java JRE 7.
• AlwaysOn enhancements
  • Up to three secondary replicas, with load balancing
  • Distributed Transaction support (DTC)
  • Support for SSISDB
• Row Level Security. Using functions, an administrator can define which rows a user can access.
• Dynamic Data Masking. Protects sensitive data by applying a mask on the returned data. For example, an email address can be returned as [email protected].
• Native JSON support. The FOR JSON clause has been added, which is similar to FOR XML.
• Temporal tables. A great new feature that allows tables to automatically track history for its rows. Think Type 2 changing dimensions, which you can just define on a table. More information will follow in a future tip.
• Query Store. A service that monitors and stores query plans along with the performance history. Very useful for performance troubleshooting!
• Enhanced columnstore indexes. For example, nonclustered columnstore indexes are now updateable. This MSDN page gives a good overview of the changes: Columnstore Indexes Versioned Feature Summary.
• Live Query Statistics. Allows you to view an execution plan live for a running query.
• Multiple tempdb database files. By default, SQL Server will now create multiple tempdb database files. One for each CPU of the server or 8, whichever is lower.

SQL Server 2016 Features - Azure

This list displays features inside the SQL Server database product that are somehow linked to the Azure cloud environment. It's not about which features are introduced in the Azure SQL Database. A lot of the features from the previous section are already included in Azure SQL Database.

• Stretch Database. A part of the table is stored in an Azure SQL Database.
• Managed Back-up: Automates your back-up to Azure. It already existed, but the underlying behavior has changed.

SQL Server 2016 Features - Business Intelligence

• Master Data Services enhancements. There are quite some changes to the back-end of MDS. You can find a lot of information on the blog of the MDS team.
  • Improved performance. This will allow you to create larger models and to load data with better performance. The add-in for Excel has also got a performance upgrade and can now handle entities with thousands of members and more.
  • Improved manageability
    • Attribute names longer than 50 characters
    • Hide or rename Code and Name attributes
  • Security has been improved. There is a new Super User function that has the same permissions as the Server Admin. More granular levels of permissions have been defined: Read, Create, Update and Delete.
  • Better transaction log maintenance
  • Improved troubleshooting
• SSAS Tabular enhancements. More details can be found on MSDN.
  • Parallel partition processing
  • New DAX functions. A few catch my eye, such as median and percentile functions, but also a function called CALENDAR(AUTO) which generated date tables and the introduction of join functions.
  • New DBCC command, which can check models (also SSAS Multidimensional) for corruption.
  • Easier set-up for SSAS in Power Pivot mode
• SSRS enhancements. More details can be found on MSDN.
  • Support of .NET Framework 4
  • Report Builder supports High DPI
  • Subscription enhancements
    • Enable and disable subscriptions (currently supported in Native mode)
    • Description (SharePoint and Native mode)
    • Change subscription owner (SharePoint and Native mode)
    • The ability to define a single file share account which can be reused in subscriptions. (Native mode)
• Incremental Package Deployment for SSIS. More details will follow in another tip.

Conclusion

A lot of new exciting features have already been introduced with SQL Server 2016 CTP2. In this tip, you have an overview of those features together with links to reference material.

The final SQL Server 2016 product will have even more features. For example, a lot of features have been announced for business intelligence that are not yet in the preview.

Next Steps

• Download the preview of SQL Server 2016 and start playing! Or spin-up a virtual machine in Azure to save you the time of installing the preview.
• Use this tip as a starting point for your learning path. Most new features are linked to reference material to get you started.
• There are already Virtual Labs available to help you explore the new features.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips