Preface

Microsoft SQL Server is developing faster than ever in its nearly 30 years history. The newest version, SQL Server 2016, brings many important new features. Some of these new features just extend or improve features that were introduced in the previous versions of SQL Server, and some of them open a completely new set of possibilities for a database developer.

This book prepares the readers for more advanced topics by starting with a quick introduction of SQL Server 2016's new features and a recapitulation of the possibilities database developers had already in the previous versions of SQL Server. Then, the new tools are introduced. The next part introduces small delights in the Transact-SQL language, then the book switches to a completely new technology inside SQL Server—JSON support. This is where the basic chapters finish, and the more complex chapters start. Stretch database, security enhancements, and temporal tables are medium-level topics. The last chapters of the book cover advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. The final two chapters introduce R and R support in SQL Server and show how to use the R language for data exploration and analysis beyond that which a developer can achieve with Transact-SQL.

By reading this book, you will explore all of the new features added to SQL Server 2016. You will be capable of identifying opportunities for using the In-Memory OLTP technology. You will learn how to use columnstore indexes to get significant storage and performance improvements for analytical applications. You will be able to extend database design by using temporal tables. You will exchange JSON data between applications and SQL Server in a more efficient way. For vary large tables with some historical data, you will be able to migrate the historical data transparently and securely to Microsoft Azure by using Stretch Database. You will tighten security by using the new security features to encrypt data or to get more granular control over access to rows in a table. You will be able to tune workload performance more efficiently than ever with Query Store. Finally, you will discover the potential of R integration with SQL Server.

What this book covers

Chapter 1, Introduction to SQL Server 2016, very covers briefly the most important features and enhancements, not only those for developers. We want to show the whole picture and point where things are moving on.

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

Chapter 3, SQL Server Tools, helps you understand the changes in the release management of SQL Server tools and explores small and handy enhancements in SQL Server Management Studio (SSMS). It also introduces RStudio IDE, a very popular tool for developing R code, and briefly covers SQL Server Data Tools (SSDT), including the new R Tools for Visual Studio (RTVS), a plugin for Visual Studio, which enables you to develop R code in an IDE that is common and well-known among developers that use Microsoft products and languages.

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

Chapter 5, JSON Support in SQL Server, explores the JSON support built into SQL Server. This support should make it easier for applications to exchange JSON data with SQL Server.

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

Chapter 7, Temporal Tables, 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 (for example, a time-travel application).

Chapter 8, Tightening the Security, introduces three new security features. With Always Encrypted, SQL Server finally enables full data encryption. Row-level security on the other side restricts which data in a table can be seen by specific user. Dynamic data masking is a soft feature that limits sensitive data exposure by masking it to non-privileged users.

Chapter 9, Query Store, guides you through Query Store, and helps you to troubleshoot and fix performance problems that are related to execution plan changes.

Chapter 10, Columnstore Indexes, revises the columnar storage and then explores the 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, describes a feature introduced in SQL Server 2014 that is still underused: the In-Memory database engine, which provides significant performance gains for OLTP workloads.

Chapter 12, In-Memory OLTP Improvements in SQL Server 2016, describes all of the improvements of the In-Memory OLTP technology in SQL Server 2016, which extend the number of potential use cases and allow implementation with less development effort and risk.

Chapter 13, Supporting R in SQL Server, introduces R Services and the R language. It explains how SQL Server R Services combine 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.

Chapter 14, Data Exploration and Predictive Modeling with R in SQL Server, shows how you can use R for advanced data exploration and manipulation, and for statistical analysis and predictive modeling that is way beyond what is possible when using T-SQL language.

What you need for this book

In order to run all of the demo code in this book, you will need SQL Server 2016 Developer or Enterprise Edition. In addition, you will extensively use SQL Server Management Studio. You will also need the RStudio IDE and/or SQL Server Data Tools with R Tools for Visual Studio plug-in.

Who this book is for

This book is aimed at database developers and solution architects who plan to use new SQL Server 2016 features or simply want to know what is now available and which limitations from previous versions have been removed. An ideal book reader is an experienced SQL Server developer, familiar with features of SQL Server 2014, but this book can be read by anyone who has an interest in SQL Server 2016 and wants to understand its development capabilities.

Conventions

In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: " One table has varchar(5) columns, which will be small enough to fit in the in-row storage."

A block of code is set as follows:

EXEC dbo.InsertSimpleOrder
@OrderId = 5, @OrderDate = '20160702', @Customer = N'CustA';
EXEC dbo.InsertSimpleOrderDetail
@OrderId = 5, @ProductId = 1, @Quantity = 50;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

ProductId INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

Any command-line input or output is written as follows:

SQL Server Execution Times:
CPU time = 1797 ms, elapsed time = 1821 ms.

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this:

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of. To send us general feedback, simply e-mail [e-mail], and mention the book's title in the subject of your message. If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support  and register to have the files e-mailed directly to you.

You can download the code files by following these steps:

1. Log in or register to our website using your e-mail address and password.

2. Hover the mouse pointer on the SUPPORT tab at the top.

3. Click on Code Downloads & Errata.

4. Enter the name of the book in the Search box.

5. Select the book for which you're looking to download the code files.

6. Choose from the drop-down menu where you purchased this book from.

7. Click on Code Download.

Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:

• WinRAR / 7-Zip for Windows

• Zipeg / iZip / UnRarX for Mac

• 7-Zip / PeaZip for Linux

The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/SQL-Server-2016-Developers-Guide. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.

To view the previously submitted errata, go to https://www.packtpub.com/books/content/support  and enter the name of the book in the search field. The required information will appear under the Errata section.

Piracy

Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at [e-mail] with a link to the suspected pirated material.

We appreciate your help in protecting our authors and our ability to bring you valuable content.

Questions

If you have a problem with any aspect of this book, you can contact us at [e-mail], and we will do our best to address the problem.