SQL Server 2017 Developer's Guide

William Durkin, Miloš Radivojević, Dejan Sarka

• Publisher: Packt Publishing
• Year: 2018
• ISBN:
  9781788479936
• Edition: 1
• Topic: Informatica
• Subtopic: Programmazione in SQL

Build smarter and efficient database application systems for your organization with SQL Server 2017

Key Features

• Build database applications by using the development features of SQL Server 2017
• Work with temporal tables to get information stored in a table at any time
• Use adaptive querying to enhance the performance of your queries

Book Description

Microsoft SQL Server 2017 is the next big step in the data platform history of Microsoft as it brings in the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. Compared to its predecessor, SQL Server 2017 has evolved into Machine Learning with R services for statistical analysis and Python packages for analytical processing. This book prepares you for more advanced topics by starting with a quick introduction to SQL Server 2017's new features and a recapitulation of the possibilities you may have already explored with previous versions of SQL Server. The next part introduces you to enhancements in the Transact-SQL language and new database engine capabilities and then switches to a completely new technology inside SQL Server: JSON support. We also take a look at the Stretch database, security enhancements, and temporal tables.

Furthermore, the book focuses on implementing advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. Towards the end of the book, you'll be introduced to R and how to use the R language with Transact-SQL for data exploration and analysis. You'll also learn to integrate Python code in SQL Server and graph database implementations along with deployment options on Linux and SQL Server in containers for development and testing.

By the end of this book, you will have the required information to design efficient, high-performance database applications without any hassle.

What you will learn

• Explore the new development features introduced in SQL Server 2017
• Identify opportunities for In-Memory OLTP technology
• Use columnstore indexes to get storage and performance improvements
• Exchange JSON data between applications and SQL Server
• Use the new security features to encrypt or mask the data
• Control the access to the data on the row levels
• Discover the potential of R and Python integration
• Model complex relationships with the graph databases in SQL Server 2017

Who this book is for

Database developers and solution architects looking to design efficient database applications using SQL Server 2017 will find this book very useful. In addition, this book will be valuable to advanced analysis practitioners and business intelligence developers. Database consultants dealing with performance tuning will get a lot of useful information from this book as well.

Some basic understanding of database concepts and T-SQL is required to get the best out of this book.

Transact-SQL and Database Engine Enhancements

Each new SQL Server version brings numerous extensions and improvements to the Transact-SQL language. Most of them are used to support newly added database engine features, but some of them address missing functionalities and limitations in previous versions. SQL Server 2016 and SQL Server 2017 come up with many features that require extensions in Transact-SQL: temporal tables, JSON support, improvements for memory-optimized tables, columnstore tables and indexes, new security enhancements, graph databases, and more. They will be explored in detail in the chapters dedicated to the appropriate features.

This chapter covers Transact-SQL features that can make a developer's work more productive and enhancements that can increase the availability of database objects and enlarge the scope of existing functionalities, limited in the previous SQL Server versions. In addition, it will cover how the execution plans in SQL Server 2017 are improved during compilation and after query execution.

This chapter is divided into the following four sections:

• New and enhanced Transact-SQL functions and expressions
• Enhanced DML and DDL statements
• New query hints
• Adaptive query processing in SQL Server 2017

In the first section, you will see new, out-of-the-box functions and expressions that allow developers to manipulate with strings more efficiently, to compress text by using the GZIP algorithm, and play with session-scoped variables.

The second section covers enhancements in data manipulation and data definition statements. The most important one will let you change the data type or other attributes of a table column, while the table remains available for querying and modifications. This is a very important feature for systems where continuous availability is required. You will also be aware of other improvements that let you perform some actions faster or with less written code.

The third section brings a demonstration of how to use newly added query hints to improve query execution and avoid problems caused by the Spool operator or inappropriate memory grants.

Finally, SQL Server 2017 introduces the adaptive query processing feature, which breaks the barrier between query plan optimization and actual execution, improves overall performance, and addresses issues that cause suboptimal execution plans.

New and enhanced functions and expressions

SQL Server 2016 and SQL Server 2017 introduce several new functions that can help developers to be more productive and efficient. Additionally, by removing limitations in some existing functions, their scope of usage has been enlarged. SQL Server now contains more than 300 built-in functions. Here is the list of new or changed functions and expressions in SQL Server 2016:

• Two new string functions: STRING_SPLIT and STRING_ESCAPE
• New date function and new expression: DATEFDIFF_BIG and AT TIME ZONE
• Four new system functions: COMPRESS, DECOMPRESS, SESSION_CONTEXT, and CURRENT_TRANSACTION_ID
• Enhancements to the HASHBYTES cryptographic function
• Four JSON related functions: ISSJON, JSON_VALUE, JSON_QUERY, and JSON_MODIFY, and one new rowset function, OPENJSON

SQL Server 2017 has introduced these string functions: STRING_AGG, TRIM, CONCATE_WS, and TRANSLATE.

Using STRING_SPLIT

Since SQL Server does not support arrays, when multiple values need to be sent to it, developers use a list of values (usually comma-separated ones).

SQL Server 2008 introduced an excellent feature called table-valued parameters (TVP), which allows you to pack values in a table and transfer them to SQL Server in table format. On the server, stored procedures or queries use this parameter as a table variable and can leverage set-based operations to improve performance, compared to separate executions per single parameter value. Thus, in all editions of SQL Server 2008 onwards it is strongly recommended to use TVP instead of a list of values in such cases.

However, lists of values as parameters for stored procedures are still widely used, mainly for the following two reasons:

• Missing support for TVP in JDBC drivers: Java applications and services still have to use comma-separated lists or XML to transfer a list of values to SQL Server
• Legacy code: Significant amounts of Transact-SQL code from the previous SQL Server versions, where TVP was not supported.

You might ask yourself why companies still have legacy code in their production systems and why they don't migrate the old code so that they can benefit from new features and enhancements. For instance, why are old implementations with comma-separated lists not replaced by the recommended TVPs? The migration steps are not complex and every developer can perform them. However, in a medium or large company, developers cannot decide what should be done. Their responsibility scope is related to how and not to what. Therefore, in such cases, developers can suggest the migration to project managers or product owners and the decision about the priority of the action is made on the business side. To migrate a comma-separated list to TVP, you need to change not only the body of stored procedures, but also their parameters and their interface. You also need to change the data access layer to touch the application code, to adjust unit tests, to compile the project, and to deploy it. Even if your tests are fully automated, this is not a trivial effort. On the other hand, the migration does not bring significant improvements for customers. Nowadays, development processes are mostly based on the agile methodology and features mostly wanted and appreciated by customers have the highest priority. Therefore, such migration actions usually remain at the bottom of the to-do list.

When a list of values is transferred to SQL Server as a stored procedure parameter, in the stored procedure body this list has to be converted to a table. Until SQL Server 2016, there was no built-in function that could perform this action. Developers had to write user-defined functions (UDF) or play with the FOR XML PATH extension for that purpose. An excellent overview and performance comparison of existing UDFs for converting a string to a table can be found in the article Split strings the right way – or the next best way, written by Aaron Bertrand. The article is available at the following address: http://sqlperformance.com/2012/07/t-sql-queries/split-strings.

Finally, the SQL Server development team added the STRING_SPLIT function into the latest release. This is a table-valued function and converts a delimited string into a single-column table. The function accepts two input arguments:

• String: An expression of any non-deprecated string data type that needs to be split
• Separator: Single character used as a separator in the input string

Since it is a table-valued function, it returns a table. The returned table contains only one column with the name value and with a data type and length that are the same as those of the input string.

Here is an example showing how this function can be used to produce a three-row table for a comma-separated list as input. Execute this code:

USE tempdb; SELECT value FROM STRING_SPLIT(N'Rapid Wien,Benfica Lisboa,Seattle Seahawks',','); 

The preceding query produces the following output:

value ------------------ Rapid Wien Benfica Lisboa Seattle Seahawks

The actual execution plan for the preceding query looks as follows:

Estimated Number of Rows for the STRING_SPLIT function

Notice that the Estimated Number of Rows is 50. This is always the case with this function: the estimated output is 50 rows and it does not depend on the number of string elements. Even when you specify the OPTION (RECOMPILE) query hint, the estimation remains the same. In the case of user-defined table-valued functions, the Estimated Number of Rows is 100.

As a table-valued function, STRING_SPLIT can be used not only in the SELECT clause, but also in FROM, WHERE, and wherever a table expression is supported. To demonstrate its usage, you will use the new SQL Server sample database: WideWorldImporters. The database is available for download at https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0. The following query extracts stock items with the 16GB tag in the Tags attribute:

USE WideWorldImporters; SELECT StockItemID, StockItemName, Tags FROM Warehouse.StockItems WHERE '"16GB"' IN (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE(Tags,'[',''), ']',''), ','));

This query produces the following result:

The following code example demonstrates how this function can be used to return details about or...