5. JSON Support in SQL Server | SQL Server 2016 Developer's Guide

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

SQL Server 2016 Developer's Guide

By : Dejan Sarka, Radivojeviƒá, William Durkin

4.5 (8)

SQL Server 2016 Developer's Guide

4.5 (8)

By: Dejan Sarka, Radivojeviƒá, William Durkin

Overview of this book

Microsoft SQL Server 2016 is considered the biggest leap in the data platform history of the Microsoft, in the ongoing era of Big Data and data science. This book introduces you to the new features of SQL Server 2016 that will open a completely new set of possibilities for you as a developer. It prepares you for the more advanced topics by starting with a quick introduction to SQL Server 2016'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 small delights in the Transact-SQL language 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. The last chapters concentrate on implementing advanced topics, including Query Store, column store indexes, and In-Memory OLTP. You will finally be introduced to R and learn how to use the R language with Transact-SQL for data exploration and analysis. By the end of this book, you will have the required information to design efficient, high-performance database applications without any hassle.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Introduction to SQL Server 2016

1. Introduction to SQL Server 2016

Security

Engine features

Programming

Business intelligence

Release cycles

Summary

2. Review of SQL Server Features for Developers

2. 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

3. SQL Server Tools

Installing and updating SQL Server tools

New SSMS features and enhancements

SQL Server Data Tools

Tools for developing R code

Summary

4. Transact-SQL Enhancements

4. Transact-SQL Enhancements

New and enhanced functions and expressions

Enhanced DML and DDL statements

New query hints

Summary

5. JSON Support in SQL Server

5. JSON Support in SQL Server

Why JSON?

What is JSON?

JSON in SQL Server prior to SQL Server 2016

Retrieving SQL Server data in the JSON format

Converting JSON data in a tabular format

JSON storage in SQL Server 2016

Validating JSON data

Extracting values from a JSON text

Modifying JSON data

Performance considerations

Summary

6. Stretch Database

6. Stretch Database

Stretch Database 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

7. Temporal Tables

What is temporal data?

System-versioned tables in SQL Server 2016

History table physical implementation

What is missing in SQL Server 2016?

Summary

8. Tightening the Security

8. Tightening the Security

SQL Server security basics

Encrypting the data

Row-Level security

Exploring dynamic data masking

Summary

9. Query Store

9. 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

Query Store use cases

Summary

10. Columnstore Indexes

10. 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

11. 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 2016

12. In-Memory OLTP Improvements in SQL Server 2016

Ch-Ch-Changes

Summary

13. Supporting R in SQL Server

13. Supporting R in SQL Server

Introducing R

Manipulating data

Understanding the data

SQL Server R services

Summary

14. Data Exploration and Predictive Modeling with R in SQL Server

14. Data Exploration and Predictive Modeling with R in SQL Server

Intermediate statistics - associations

Advanced analysis - undirected methods

Advanced analysis - directed methods

Advanced graphing

Summary

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...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

SQL Server 2016 Developer's Guide

Search

Your notes and bookmarks