Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • SQL Server 2017 Developer???s Guide
  • Toc
  • feedback
SQL Server 2017 Developer???s Guide

SQL Server 2017 Developer???s Guide

3.6 (5)
close
SQL Server 2017 Developer???s Guide

SQL Server 2017 Developer???s Guide

3.6 (5)

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.
Table of Contents (19 chapters)
close
close
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
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
chevron up
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
18
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

JSON Support in SQL Server

In the last few years, JSON has been established as a standard format for data exchange among applications and services. XML is still the exchange standard (and will remain so), but many applications communicate by exchanging JSON data instead of XML documents. Therefore, the most important relational database management system products need to support JSON.

Two release cycles after the feature was requested by the community, Microsoft has implemented built-in JSON support in SQL Server 2016. The support is not as comprehensive as for XML, but for most databases and workloads, it will be quite fine.

This chapter explores how SQL Server stores and processes JSON data, with a comprehensive comparison between JSON and XML support in SQL Server.

The most important actions related to JSON data in SQL Server are demonstrated in detail:

  • Formatting and...
End of Section 1
Next Section
bookmark search playlist download
font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete