Book Image

SQL Server 2017 Developer???s Guide

Book Image

SQL Server 2017 Developer???s Guide

Overview of this book

Microsoft SQL Server 2017 is a milestone in Microsoft's data platform timeline, as it brings in the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. This book prepares you for advanced topics by starting with a quick introduction to SQL Server 2017's new features. Then, it introduces you to enhancements in the Transact-SQL language and new database engine capabilities before switching to a different technology: JSON support. You will take a look at the security enhancements and temporal tables. Furthermore, the book focuses on implementing advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. Toward 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 into SQL Server and graph database implementations as well as the deployment options on Linux and SQL Server in containers for development and testing. By the end of this book, you will be armed to design efficient, high-performance database applications without any hassle.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2017 Developer???s Guide
Mar, 2018 | 816 pages
No titles found
Table of Contents (25 chapters)
Title Page
Title Page
Copyright and Credits
Copyright and Credits
Dedication
Dedication
Packt Upsell
Packt Upsell
Contributors
Contributors
Preface
Preface
Free Chapter
1
Introduction to SQL Server 2017
Introduction to SQL Server 2017
Security
Summary
2
Review of SQL Server Features for Developers
Review of SQL Server Features for Developers
The mighty Transact-SQL SELECT
DDL, DML, and programmable objects
Transactions and error handling
Beyond relational
Summary
3
SQL Server Tools
SQL Server Tools
Installing and updating SQL Server Tools
New SSMS features and enhancements
SQL Server Data Tools
Tools for developing R and Python code
Summary
4
Transact-SQL and Database Engine Enhancements
Transact-SQL and Database Engine Enhancements
New and enhanced functions and expressions
Enhanced DML and DDL statements
New query hints
Adaptive query processing in SQL Server 2017
Summary
5
JSON Support in SQL Server
JSON Support in SQL Server
Why JSON?
What is JSON?
JSON in SQL Server prior to SQL Server 2016
Retrieving SQL Server data in JSON format
Converting JSON data in a tabular format
JSON storage in SQL Server 2017
Validating JSON data
Extracting values from a JSON text
Modifying JSON data
Performance considerations
Summary
6
Stretch Database
Stretch Database
Stretch DB architecture
Limitations of using Stretch Database
Use cases for Stretch Database
Enabling Stretch Database
Querying stretch databases
SQL Server Stretch Database pricing
Stretch DB management and troubleshooting
Summary
7
Temporal Tables
Temporal Tables
What is temporal data?
System-versioned temporal tables in SQL Server 2017
What is missing in SQL Server 2017?
Summary
8
Tightening Security
Tightening Security
SQL Server security basics
Encrypting the data
Row-Level Security 
Exploring dynamic data masking
Summary
9
Query Store
Query Store
Why Query Store?
What is Query Store?
Query Store architecture
Enabling and configuring Query Store
Disabling and cleaning Query Store
Query Store in action
Query Store reports in SQL Server Management Studio
Automatic tuning in SQL Server 2017
Capturing waits by Query Store in SQL Server 2017
Query Store use cases
Summary
10
Columnstore Indexes
Columnstore Indexes
Analytical queries in SQL Server
Columnar storage and batch processing
Nonclustered columnstore indexes
Clustered columnstore indexes
Summary
11
Introducing SQL Server In-Memory OLTP
Introducing SQL Server In-Memory OLTP
In-Memory OLTP architecture
Creating memory-optimized tables and indexes
Querying and data manipulation
Data durability concerns
Database startup and recovery
Management of In-Memory objects
Extended events
PerfMon counters
Summary
12
In-Memory OLTP Improvements in SQL Server 2017
In-Memory OLTP Improvements in SQL Server 2017
Ch-Ch-Changes
Feature improvements
Improvements in the In-Memory OLTP engine
Summary
13
Supporting R in SQL Server
Supporting R in SQL Server
Introducing R
Manipulating data
Understanding data
SQL Server R Machine Learning Services
Summary
14
Data Exploration and Predictive Modeling with R
Data Exploration and Predictive Modeling with R
Intermediate statistics – associations
Advanced analysis – undirected methods
Advanced analysis – directed methods
Advanced graphing
Summary
15
Introducing Python
Introducing Python
Starting with Python
Working with data
Data science with Python
Summary
16
Graph Database
Graph Database
Introduction to graph databases
Graph features in SQL Server 2017
SQL Graph limitations
Summary
17
Containers and SQL on Linux
Containers and SQL on Linux
Containers
SQL Server on Linux
Summary
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
Index

Modifying JSON data

You might sometimes need to update only a part of JSON data. In SQL Server 2016, you can modify JSON data using the JSON_MODIFY function. It allows you to:

  • Update the value of an existing property
  • Add a new element to an existing array
  • Insert a new property and its value
  • Delete a property based on a combination of modes and provided values

The function accepts three mandatory input arguments:

  • Expression: This is a variable or column name containing JSON text.
  • Path: This is the JSON path expression with an optional modifier append.
  • new_value: This is the new value for the property specified in the path expression.

The JSON_MODIFY function returns the updated JSON string. In the next subsections, you will see this function in action.

Adding a new JSON property

In the following code example, you add a new property named IsVinyl with the value true:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975 
}'; 
PRINT JSON_MODIFY(@json, '$.IsVinyl', CAST(1 AS BIT...
Previous Section
End of Section 10
Next Section