Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Features in CTP2


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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
  • 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 XXX@XXXX.com.
  • 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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools