SQL Server 2016 Developer's Guide
Article Index
SQL Server 2016 Developer's Guide
Chapters 7 -14, Conclusion

Author: Dejan Sarka et al
Publisher: Packt Publishing
Pages: 616
ISBN: 978-1786465344
Print: 1786465345
Kindle: B01MS5L01Q
Audience: SQL Server devs & architects
Rating: 4.8
Reviewer: Ian Stirk

This book aims to introduce you to the salient new and enhanced features in SQL Server 2016, how does it fare?

This book is aimed at SQL Server developers and architects that want to understand the latest features in SQL Server 2016. The ideal reader would be an intermediate-level developer with experience of SQL Server 2014, that said, the book does cover some background material from previous versions of SQL Server where appropriate.

In many ways, SQL Server 2016 removes many of the limitations found in SQL Server 2014 (especially In-Memory OLTP), as well as implementing other useful developer features.

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1 Introduction to SQL Server 2016

The book’s opening chapter provides an overview of the new and enhanced SQL Server 2016 features that are subsequently detailed in the rest of the book. There’s a brief overview of the recent history of SQL Server, culminating in Microsoft’s “Mobile First, Cloud First” strategy. Next, security enhancements are highlighted (row level security, dynamic data masking, always encrypted).

The chapter continues with an overview of the major SQL Server engine enhancements, namely:

  • Query Store (troubleshooting sudden performance changes)

  • Live Query Statistics (real-time analysis of performance)

  • Stretch Database (offload older data to slower/cheaper storage)

  • Database scoped configuration (some server-level settings now set at database-level)

  • Temporal Tables (easier table history/versioning)

  • Columnstore indexes (now updateable)

Programming enhancements are outlined next, including new T-SQL functions, and JSON (a “better” XML). Business Intelligence has been enhanced with the incorporation of the language R into SQL Server. The chapter ends with a look at the increasing frequency of software releases.

This chapter provides a useful, if very brief, overview of the major new and enhanced features in SQL Server 2016, it also highlights what to expect from the rest of the book, where these features are examined in greater detail. It is generally easy to read, with useful explanations, tips, example code, and diagrams. These traits apply to the whole of the book.

Chapter 2 Review of SQL Server Features for Developers

Before explaining the new and enhanced developer features in SQL Server 2016, this chapter aims to bring everyone up to the same level of understanding by reviewing the features in previous versions of SQL Server. It looks at various advanced SELECT techniques (e.g. ranking), creating and altering database objects (e.g. triggers, views), transaction usage, and error handling and ends with a look at features that extend the relational model (i.e. spatial data, CLR usage, and XML usage).

This chapter provides a useful recap on pre-SQL Server 2016 features, additionally it may prove useful since not everyone will have experience of the features in recent versions of SQL Server. Alternatively, it might be argued there Is no need for this chapter in a book about new and enhanced SQL Server 2016 features.

As it contains plenty of helpful example code to illustrate the SQL features being discussed, this chapter reiterates this is not a book for beginners.



Chapter 3 SQL Server Tools

Knowing the functionality of your development tools can enhance your productivity, this chapter discusses the new and enhanced features in the various tools. Microsoft has helpfully separated the release cycle of SQL Server from its tools, thus facilitating faster bug fixes and the implementation of new features.

The chapter opens with a look at the installation of SQL Server Management Studio (SSMS), including useful screenshots. Some new and enhanced SSMS features are then discussed, including:


  • Autosave open tabs

  • Searchable options (search tool settings/menus)

  • Enhanced scroll bar (e.g. indicate lines with syntax errors)

  • Execution plan comparison (useful for performance tuning)

  • Live Query Statistics (useful for real-time troubleshooting)


The chapter then looks at the installation of SQL Server Data Tools (SSDT), again with useful screenshots. SSDT is typically used in workflow and integration solutions. Next it looks at the installation of RStudio IDE and R Tools for Visual Studio, environments for the R language.  

I’m very keen that books should include satisfactory installation information. If you’re learning something new, you will not get very far if the installation instructions are absent or inadequate. This chapter provides a useful overview of the installation process for the various SQL Server 2016 tools. Each of the tools discussed has much more functionality than given in this chapter, however it does provide an adequate, if brief, introduction.


Chapter 4 Transact-SQL Enhancements

New versions of SQL Server have new and enhanced SQL code functionality, some of these features are examined here, including:






  • JSON functions

In each case, the function is explained and its use shown with helpful SQL code.

The chapter next looks at enhanced Data Manipulation Language (DML) and Data Definition Language (DDL) statements. For the most part, these are nice to have improvements (e.g. DROP IF EXISTS), but alternatives already exist for many of these enhancements.

The chapter ends with a look at three new query hints. Hints are not actually hints, but are commands, they force the optimizer to follow your instructions, while this may be useful, they should be examined regularly since their use may change. Helpfully, the authors highlight this concern. Again this chapter provides a useful overview of new and enhanced SQL features.


Chapter 5 JSON Support in SQL Server

JSON (JavaScript Object Notation) is a mechanism of data exchange (cf. XML), increasingly used by applications. This chapter explores what JSON is, how it is used, converting JSON data into table data, extracting data values, and modifying JSON data. It ends with a look at performance concerns associated with the current implementation of JSON. In many ways, the use of JSON is similar to how XML is used by SQL Server (but without native support). Overall this chapter should prove useful in extending the reach of JSON-based applications.


Chapter 6 Stretch Database

As data volumes increase, query performance can decrease. Stretch Database functionality allows part of your data (the older, less queries data) to be moved to slower/cheaper storage in the cloud (Microsoft Azure), aiming to improve performance. This is done seamlessly to any application use.

The chapter opens with an overview of the Stretch Database architecture, which determines if the query runs locally and/or remotely. The Data Migration Assistant can be used to identify databases and tables that would benefit from stretching, its use is demonstrated with helpful screenshots.

Various limitations of Stretch Database are discussed. Microsoft often releases a new feature with limited capabilities, that is subsequently enhanced. Some user cases of expected usage are provided (e.g. archiving). Some example SQL code is provided to enable Stretch Database functionality. The chapter ends with a discussion of management and troubleshooting features, rounding out a useful introduction to a feature that may well save you money/resources and improve the performance of your queries.

Last Updated ( Tuesday, 04 July 2017 )