Introducing SQL Server 2016 – the best SQL Server yet!

According to Rob, SQL Server 2016 is a significant improvement on 2012 and 2014 with a broad range of new capabilities being introduced right across the platform that are particularly relevant for modern businesses. These are the top features he is picking, however they are just the tip of the iceberg with many improvements to existing capabilities and additional new features.

New T-SQL functions:

• Built in support for JSON (using FOR JSON)
• Polybase to access data stored in Hadoop or Azure Blob Storage
• STRING_SPLIT function to break delimited strings
• STRING_ESCAPE function to deal with escaping characters in strings

‍Integration Services

• SAP BW connector
• Explicit support for Excel (2013\2016)
• Manage and maintain packages in multiple versions (2012, 2014 or 2016)

Security

• Row level security for tables
• Always encrypted
• Dynamic data masking

Setup‍

• Ability to configure tempdb in the right place upfront!

Analytics

• Support for R with SQL Server R Services
• Display folders in Tabular models
• A range of new DAX functions
• DirectQuery mode now supports connections to Oracle, Teradata and Microsoft Analytics Platform (formerly known as Parallel Data Warehouse).

Availability

• Hybrid mode (stretch database) – hot data can be onsite and cold data in the cloud, and no application changes required.
• Replication to Azure SQL database

Reporting

• Complete redesign of SQL Server Reporting Services portal
• Mobility focused release
• Cloud and on-premise hybrid models (pinning reports into Power BI)
• No more SharePoint integrated mode required
• HTML 5 compliant
• Tree maps and sunburst charts
• Export to PowerPoint

Performance improvements in SQL Server 2016

Richard focused more on the performance side, and improvements to In-Memory OLTP (Online Transaction Processing) since it was first introduced in SQL Server 2014, that allow data to be accessed and processed much more efficiently.

In-memory OLTP will primarily improve the performance of programs where there are many inserts and updates, and business logic in stored procedures. It integrates with SQL Server and Azure SQL Database supporting both memory-optimised tables and disk-based tables in the same database, and includes querying across both types of tables.

In-memory OLTP manages to achieve these significant performance gains via:

• Optimised algorithms for accessing memory-resident data
• Optimistic concurrency control that eliminates logical locks
• Lock free objects that eliminate all physical locks and latches
• Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures when accessing a memory-optimised table

A great article highlighting the performance benefits of natively compiled stored procedures can be found here: Natively Compiled Stored Procedures: What they are all about

Improvements in SQL Server 2016 include:

• Maximum memory for memory-optimized tables
• Schema and data changes - ALTER TABLE can be used on memory-optimized tables to add, drop or alter columns, or to add, drop or rebuild indexes
• Parallel plans - certain operations that use hash indexes can be performed in parallel, if they’re not used in a natively compiled procedure
• Support for Transparent Data Encryption - Memory-optimised tables data on disk will be encrypted

- Natively Compiled procedures will ALSO support a wider range of features for performance improvements:

• LEFT and RIGHT OUTER JOIN
• SELECT DISTINCT
• OR and NOT operators
• Subqueries in all clauses of a SELECT statement
• Nested stored procedure calls
• UNION and UNION ALL
• All built-in math functions

These are just the few features that Rob and Richard think make this the best release of SQL ever, however there are hundreds more all worth exploring and this is a great place to start https://msdn.microsoft.com/en-us/library/bb500435.aspx.