Level: Introductory to Intermediate

Thomas LeBlanc

Data Warehouse Independent Consultant

Data on the Geaux

SQL Server 2016 has added and improved features to an already great product. To start, there is now a Query Store to retrieve history of a query's execution plan and statistics used for that plan. You can compare them to see the changes. You can also see the Execution Plans "Live" to see where a long running query is spending lots of time. You can also compare plans side-by-side, which should make DBAs or anyone that performance tune queries very happy.

From there, you'll go into some of the database design aspects of 2016 to improve table implementation. In-Memory has been half-baked in previous versions and now is enterprise ready with OLTP tables. The Column-store indexes include update-able clustered and non-clustered indexes. Temporal Tables remove the requirement of using triggers or custom code to find a point in time version of the data. You now can mask columns for limited viewing and casting of data to the end users by login permissions.

The T-SQL enhancements will help with better writing of set-based queries. The stretch database feature will assist in archiving data to the cloud with access in applications. Always Encrypted secures the database for abiding to regulations in health care and finance. The last feature will be row-level security, which has been a frequently requested option.

You will learn:

• About T-SQL enhancements in SQL Server 2016
• About Table design changes in SQL Server 2016
• About Query Plan improvements in SQL Server 2016