Microsoft SQL Server is developing faster than ever before in its almost 30-year history. The latest versions, SQL Server 2016 and 2017, bring with them 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 its readers for more advanced topics by starting with a quick introduction to SQL Server 2016 and 2017's new features and a recapitulation of the possibilities database developers already had in previous versions of SQL Server. It then goes on to, the new tools are introduced. The next part introduces small delights in the Transact-SQL language. The book then switches to a completely new technology inside SQL Server—JSON support. This is where the basic chapters end and the more complex chapters begin. Stretch Database, security enhancements, and temporal tables are medium-level topics. The latter chapters of the book cover advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. The next two chapters introduce R and R support in SQL Server, and show how to use the R language for data exploration and analysis beyond what a developer can achieve with Transact-SQL. Python language support is then introduced. The next chapter deals with new possibilities for using data structures called graphs in SQL Server 2017. The final chapter introduces SQL Server on Linux and in containers.
By reading this book, you will explore all of the new features added to SQL Server 2016 and 2017. You will become capable of identifying opportunities for using the In-Memory OLTP technology. You will also learn how to use columnstore indexes to get significant storage and performance improvements for analytical applications. You will also be able to extend database design using temporal tables. You will learn how to exchange JSON data between applications and SQL Server in a more efficient way. For very 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 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, and use SQL Server on Linux platforms and in containers. Finally, you will discover the potential of R and Python integration with SQL Server.
Database developers and solution architects looking to design efficient database applications using SQL Server 2017 will find this book very useful. Some basic understanding of database concepts and T-SQL is required to get the best out of this book.
Chapter 1, Introduction to SQL Server 2017, very briefly covers the most important features and enhancements, not just those for developers. The chapter shows the whole picture and point readers in the direction of where things are moving.
Chapter 2, Review of SQL Server Features for Developers, brief recapitulates 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 popular among developers using Microsoft products and languages. The chapter introduces Visual Studio 2017 and shows how it can be used it for data science applications with Python.
Chapter 4, Transact-SQL and Database Engine 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 frequently/infrequently accessed data transparently and securely to Microsoft Azure 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 explain how this is implemented in SQL Server and demonstrate some use cases for it (for example, a time-travel application).
Chapter 8, Tightening Security, introduces three new security features. With Always Encrypted, SQL Server finally enables full data encryption. Row-level security on the other hand, restricts which data in a table can be seen by a specific user. Dynamic data masking is a soft feature that limits the exposure of sensitive data by masking it to non-privileged users.
Chapter 9, Query Store, guides you through Query Store and helps you troubleshoot and fix performance problems related to execution plan changes.
Chapter 10, Columnstore Indexes, revises columnar storage and then explores the huge improvements relating to columnstore indexes in SQL Server 2016: updatable non-clustered 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. This provides significant performance gains for OLTP workloads.
Chapter 12, In-Memory OLTP Improvements in SQL Server 2017, describes all the improvements to the In-Memory OLTP technology in SQL Server 2017. These improvements 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. The chapter 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, statistical analysis, and predictive modeling. All this is way beyond what is possible when using the T-SQL language.
Chapter 15, Introducing Python, teaches the Python basics and how to use the language inside SQL Server.
Chapter 16, Graph Databases, provides an overview of graph database architecture and how to create database objects in SQL Server 2017.
Chapter 17, Containers and SQL on Linux, introduces the two technologies mentioned in the chapter title and provides an overview of how to get started using them both.
- In order to run all of the demo code in this book, you will need SQL Server 2017 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
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
- Log in or register at www.packtpub.com.
- Select the
SUPPORTtab. - Click on
Code Downloads & Errata. - Enter the name of the book in the
Searchbox and follow the onscreen instructions.
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-Server2017-Developer-s-Guide. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available athttps://github.com/PacktPublishing/. Check them out!
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://www.packtpub.com/sites/default/files/downloads/SQLServer2017DevelopersGuide_ColorImages.pdf.
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "The simplest query to retrieve the data that you can write includes the SELECT and the FROM clauses. In the SELECT clause, you can use the star character (*), literally SELECT *, to denote that you need all columns from a table in the result set."
A block of code is set as follows:
USE WideWorldImportersDW; SELECT * FROM Dimension.Customer;
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
USE WideWorldImporters; CREATE TABLE dbo.Product ( 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) ) WITH (SYSTEM_VERSIONING = ON);
Any command-line input or output is written as follows:
Customer SaleKey Quantity------------------------------ -------- -----------Tailspin Toys (Aceitunas, PR) 36964 288Tailspin Toys (Aceitunas, PR) 126253 250Tailspin Toys (Aceitunas, PR) 79272 250
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Go to Tools | Options and you are then able to type your search string in the textbox in the top-left of the Options window."
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.