Category - SQL Server 2016

SQL Server 2016 Developer’s Guide

Together with Dejan Sarka (blog | twitter) and William Durkin (blog | twitter) I have finished writing on the SQL Server 2016 Developer’s Guide book.

B05694The book is available at the Packt Publishing portal and Amazon

Here is a very brief description of all chapters of the book.
Chapter 1: Introduction to SQL Server 2016

Many new improvements have been made to SQL Server 2016. In this chapter we’ll cover very briefly most important features and enhancements, not only those for developers. We want to show the whole picture and to point where the things are moving on. Although this book is for developers and covers developer related features, it is pretty clear that in 5-10 years all developers need to deal with some features which are currently developed and are under development within the business intelligence scope. Therefore, it is important to show tendencies so that everyone could consider to embrace some of them. We will also present how Microsoft plan to deliver services and products in the future.

Chapter 2: Review of SQL Server Features for Developers
A brief recapitulation of the features available for developers in previous versions of SQL Server in this chapter serves as a foundation for explanation of the many new features in SQL Server 2016. Some best practices are covered as well.

Chapter 3: SQL Server Tools
Understanding changes in the release management of SQL Server tools and exploring small and handy enhancements in SQL Server Management Studio (SSMS). Using new fancy feature live query statistics. Exploring SQL Server Data Tools (SSDT) and its support for continuous integration and deployment automation.

Chapter 4: Transact-SQL Enhancements
Exploring Transact-SQL enhancements: new functions and syntax extensions, discovering ALTER TABLE improvements for online operations and considering new query hints for query tuning.

Chapter 5: JSON Support
Supporting JSON data was the most requested feature on the Microsoft SQL Server connect site. This feature has been finally added in SQL Server 2016. Having JSON support built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.

Chapter 6: Stretch Database
Understanding how to migrate historical or less accessed data transparently and securely to the Microsoft Azure by using Stretch Database (Stretch DB) feature.

Chapter 7: Temporal Tables
SQL Server 2016 introduces support for system-versioned temporal tables based on the SQL:2011 standard. We’ll explain how this implemented in SQL Server is and demonstrates some use cases for it (time-travel application). We’ll also discuss what is still missing for the full Temporal Data support in SQL Server

Chapter 8: Tightening the Security
SQL Server 2016 introduces three new security features. With Always Encrypted SQL Server finally enables full data encryption, so that no tools or persons regardless their database and server permissions can read encrypted data except the client application with an appropriate key. Row-level security on the other side restricts which data in a table can be seen by specific user. This is very useful in multi-tenant environments where you usually want to avoid data-reading intersection between different customers. Dynamic data masking is a soft feature that limits sensitive data exposure by masking it to non-privileged users.

Chapter 9: Query Store
Understanding how to use Query Store to troubleshoot and fix performance problems that are related to execution plan changes. Although this is primarily DBA feature, it can be also very useful for developers to identify most expensive and queries with regressed (or heavily changed) execution plans. It will also help them to analyze and become more familiar with the workload patterns generated by their applications and services.

Chapter 10: Columnstore Indexes
Columnar storage was first added to SQL Server in version 2012. It included nonclustered columnstore indexes (NCCI) only. Clustered columnstore indexes (CCI) were added in version 2014. In this chapter, the readers revise the columnar storage and then explore huge improvements for columnstore indexes in SQL Server 2016: updateable nonclustered columnstore indexes, columnstore indexes on in-memory tables, and many other new features for operational analytics.

Chapter 11: Introducing SQL Server In-Memory OLTP
Understanding in SQL Server 2014 introduced, but still underused In-Memory database engine that provides significant performance gains for OLTP workloads.

Chapter 12: In-Memory OLTP Improvements in SQL Server 2016
With the new SQL Server 2016 release many of the issues that might block the adoption of In-Memory OLTP have been eliminated: supporting foreign keys, check and unique constraints, parallelism, recommended maximum size of In-Memory tables has been increased to 2 TB, tables, stored procedures and indexes can be altered… Also Transact-SQL constructs support for by In-Memory tables and compiled stored procedures has been extended. All these improvements extend the number of potential use cases and allow the implementation with less development effort and risk.

Chapter 13: Supporting R in SQL Server
SQL Server R Services combines the power and flexibility of the open source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization. This chapter introduces the R Services and the R language.

Chapter 14: Data Exploration and Predictive Modeling with R in SQL Server
Just knowing that you can use the R language inside SQL Server does not help much. After R and R support in SQL Server were introduced in the previous chapter, this chapter shows how you can use R for advanced data exploration and manipulation and for statistical analysis and predictive modeling way beyond the possibilities when using T-SQL language only.

I hope, you’ll enjoy reading it.