New Features in SQL Server 2016 – Part 1: Database Engine

In early May 2015, SQL Server 2016 was made available in Preview.  To download the CTP2 version, click here. 

This blog will list all of the new Database Engine features coming next year.  In futures posts, I will pick a couple of the enhancements and demonstrate how they will benefit DBA’s, Developers and BI specialists.  In the meantime, I am aiming to give the SQL community a high level understanding of what is new in 2016 and help people think about how some of them could benefit their clients and stakeholders.

Database Engine Feature Enhancements

There are lots of new, exciting additions to the database engine in SQL Server 2016.  They can be found below.  I have picked out my favourite five and provided additional detail on them.

· ·        Transact-SQL Enhancements

o   Columnstore Indexes

o   In-Memory OLTP

o   Live Query Statistics

o   Query Store

o   Temporal Tables

o   Backup to Microsoft Azure

o   Managed Backup

o   Trace Flag 4199

o   FOR JSON

o   Always Encrypted

o   PolyBase

o   Stretch Database

·        Transact-SQL Enhancements

·        System View Enhancements

·        Security Enhancements

o   Row-Level Security

o   Dynamic Data Masking

o   New Permissions

o   Transparent Data Encryption (TDE)

·        High Availability Enhancements

·        Tools Enhancements

Columnstore Indexes

Whilst SQL Server 2014 offered some improvements on 2012, the 2016 version is taking Columnstore indexes to the next level.  In addition, Azure SQL Databases can incorporate almost every feature in a standard on premise database.

1.       A table can have one updateable nonclustered columnstore index.

2.       The nonclustered columnstore index definition now supports filtered conditions.  As a result, the performance impact pm am OLTP table will be minimal.

3.       An in-memory table can have one columnstore index. Previously, only a disk-based table could have a columnstore index.

4.       A clustered columnstore index can have one or more nonclustered indexes. Previously, the columnstore index did not support nonclustered indexes.

5.       Supports primary and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index.

In-Memory OLTP

The current In-Memory-OLTP offers up to 30x transactions.  In SQL Server 2016, you will also be able to apply it to more applications and benefit from increased concurrency.  In addition, you can use the new in-memory columnstore with in-memory OLTP, delivering 100x faster queries.   

There are other benefits to the refined In-memory OLTP:

1.       Support ALTER operations for memory-optimized tables and natively compiled stored procedures.

2.       Support for natively compiled, scalar user-defined functions.

3.       Support for all Collations.

4.       Storage Improvements.

a.       Ability to estimate memory requirements for memory optimised tables.

b.       Once you determine the size, you need to provide disk space that is four times the size of durable, in-memory tables.

5.       Enhancements to transaction performance analysis reports.

a.       Transaction performance collector in SQL Server Management Studio helps you evaluate if In-Memory OLTP.

b.       Use the ‘Memory Optimization Advisor’ to help migrate table to in memory.

6.       Support for subqueries and query surface area in natively compiled stored procedures

a.       E.g. BETWEEN, GROUP BY, ORDER BY, TOP, UPDATE, TRY/CATCH, etc.

b.       The usual DISTINCT and ORDER BY do not work together.

c.       Neither does combining TOP and PERCENT in a Select statement.

System View Enhancements

There are a number of views that will aid a developer, especially around security and query stats.  The enhancements are grouped into 3 sections.

1.       Row Level Security

a.       sys.security_predicates – Returns a row for each security predicate in a database.

b.       sys.security_policies – Returns a row for each security policy in the database.

2.       Query Store Catalog Views

a.       7 new Query Store support views

                                                               i.      sys.database_query_store_options

                                                             ii.      sys.query_context_settings

                                                           iii.      sys.query_store_plan

                                                           iv.      sys.query_store_query

                                                             v.      sys.query_store_query_text

                                                           vi.      sys.query_store_runtime_stats

                                                          vii.      sys.query_store_runtime_stats_interval

3.       Query Hints

a.       MIN_GRANT_PERCENT

b.      MAX_GRANT_PERCENT

Row-Level Security (RLS)

Microsoft’s explanation of row-level security reads:

“Row level security (RLS) introduces a flexible, centralised, predicate-based evaluation that considers metadata or any other criteria the administrator determines as appropriate. This is used as a criterion to determine whether or not the user has the appropriate access to the data based on user attributes. “

This can be translated to something a little easier to ingest:

1.       Ability to control access to rows in a database table based on the characteristics of the user executing a query.

a.       E.g. Group Memberships, such as company department or job status.

2.       Simplifies the design and coding of application security.

3.       Restriction logic is found in the database layer, as opposed to an application.  It helps limit the various levels of required security and therefore, increases reliability.

4.       Use ‘CREATE SECURITY POLICY’ T-SQL to set up RLS.

 

High Availability Enhancements

If you are not familiar with High Availability in SQL Server 2012/14, click here for a quick introduction.  For everyone else, the below enhancements greatly enrich this feature:

1.       Load-balancing is now possible across a set of read-only replicas.  Previously, it used to always direct connections to the first available read-only replica.

2.       There are now 3 replicas that support automatic failover cluster.  It used to be 2.

3.       Group Managed Service Accounts now facilitate AlwaysOn Failover Clusters. *

4.       AlwaysOn Availability Groups can be configured to failover when a database goes offline.  You must change the setting ‘DB_FAILOVER’ option to ON.

 

*             You will need to update Windows Server R2 to avoid any downtime after a password change.

Future Blogs

As the Database Engine contains the majority of changes, I have included the other features in a series of other blog posts.  Click the links below to access them.

1.       Part 2 – Analysis Services – TBC

2.       Part 3 – Reporting Services – TBC

3.       Part 4 – Integration Services – TBC

References

For more information on all of the new SQL Server 2016 features, the below resources/blogs are highly recommended.

·        What’s New in SQL Server 2016 (Official Microsoft) –
https://msdn.microsoft.com/en-us/library/bb500435(v=sql.130).aspx

·        T.K. Ranga Rengarajan’s SQL Server Blog – http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx

·        Jen Underwood SQL Server BI Blog –
http://sqlmag.com/blog/what-coming-sql-server-2016-business-intelligence