Author: Dejan Sarka et al
Publisher: Packt Publishing
Pages: 816
ISBN: 978-1788476195
Print: 1788476190
Kindle: B078WBVRN8
Audience: SQL Server devs & architects
Rating: 4.2
Reviewer: Ian Stirk

This book’s title is misleading, it actually describes the salient new and enhanced features in both SQL Server 2016 and 2017. In other respects, how does it fare?

This book is aimed at SQL Server developers and architects who want to understand the latest features in SQL Server 2016 and 2017. 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 essence, SQL Server 2017 adds features relating to the Python programming language, using graph databases, and running SQL Server on Linux, whereas SQL Server 2016 removes many of the limitations found in SQL Server 2014 (especially In-Memory OLTP), although both implement many other useful features.

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

Chapter 1 Introduction to SQL Server 2017

The book’s opening chapter provides an overview of the new and enhanced features in SQL Server 2016 and 2017, which 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 (faster reporting)

• Containers and SQL Server on Linux (easier deployment and a new platform) 

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 (I would expect python to be mentioned preferentially here, for an emphasis on SQL Server 2017 functionality – maybe lazy editing). 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 and 2017, 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 and 2017, 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 and 2017 features.

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)

• Importing flat file wizard (simpler than import wizard)

• Vulnerability assessment (highlighting potential problems)

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. Lastly, brief details about the setting up of Visual Studio 2017 for a python data science environment are outlined.  

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 and 2017 tools. Each of the tools discussed has much more functionality than given in this chapter, however it does provide an adequate, if brief, introduction.