SQL Server 2017 Developer’s Guide电子书

Title Page

Copyright and Credits

SQL Server 2017 Developer's Guide

Dedication

Packt Upsell

Why subscribe?

PacktPub.com

Contributors

About the authors

About the reviewer

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

Introduction to SQL Server 2017

Security

Row-Level Security

Dynamic data masking

Always Encrypted

Engine features

Query Store

Live query statistics

Stretch Database

Database scoped configuration

Temporal Tables

Columnstore indexes

Containers and SQL Server on Linux

Programming

Transact-SQL enhancements

JSON

In-Memory OLTP

SQL Server Tools

Business intelligence

R in SQL server

Release cycles

Summary

Review of SQL Server Features for Developers

The mighty Transact-SQL SELECT

Core Transact-SQL SELECT statement elements

Advanced SELECT techniques

DDL, DML, and programmable objects

Data definition language statements

Data modification language statements

Triggers

Data abstraction—views, functions, and stored procedures

Transactions and error handling

Error handling

Using transactions

Beyond relational

Spatial data

CLR integration

XML support in SQL Server

Summary

SQL Server Tools

Installing and updating SQL Server Tools

New SSMS features and enhancements

Autosave open tabs

Searchable options

Enhanced scroll bar

Execution plan comparison

Live query statistics

Importing flat file Wizard

Vulnerability assessment

SQL Server Data Tools

Tools for developing R and Python code

RStudio IDE

R Tools for Visual Studio 2015

Setting up Visual Studio 2017 for data science applications

Summary

Transact-SQL and Database Engine Enhancements

New and enhanced functions and expressions

Using STRING_SPLIT

Using STRING_ESCAPE

Using STRING_AGG

Handling NULLs in the STRING_AGG function

The WITHIN GROUP clause

Using CONCAT_WS

Using TRIM

Using TRANSLATE

Using COMPRESS

Using DECOMPRESS

Using CURRENT_TRANSACTION_ID

Using SESSION_CONTEXT

Using DATEDIFF_BIG

Using AT TIME ZONE

Using HASHBYTES

Using JSON functions

Enhanced DML and DDL statements

The conditional DROP statement (DROP IF EXISTS)

Using CREATE OR ALTER

Resumable online index rebuild

Online ALTER COLUMN

Using TRUNCATE TABLE

Maximum key size for nonclustered indexes

New query hints

Using NO_PERFORMANCE_SPOOL

Using MAX_GRANT_PERCENT

Using MIN_GRANT_PERCENT

Adaptive query processing in SQL Server 2017

Interleaved execution

Batch mode adaptive memory grant feedback

Batch mode adaptive joins

Disabling adaptive batch mode joins

Summary

JSON Support in SQL Server

Why JSON?

What is JSON?

Why is it popular?

JSON versus XML

JSON objects

JSON object

JSON array

Primitive JSON data types

JSON in SQL Server prior to SQL Server 2016

JSON4SQL

JSON.SQL

Transact-SQL-based solution

Retrieving SQL Server data in JSON format

FOR JSON AUTO

FOR JSON PATH

FOR JSON additional options

Add a root node to JSON output

Include NULL values in the JSON output

Formatting a JSON output as a single object

Converting data types

Escaping characters

Converting JSON data in a tabular format

OPENJSON with the default schema

Processing data from a comma-separated list of values

Returning the difference between two table rows

OPENJSON with an explicit schema

Import the JSON data from a file

JSON storage in SQL Server 2017

Validating JSON data

Extracting values from a JSON text

JSON_VALUE

JSON_QUERY

Modifying JSON data

Adding a new JSON property

Updating the value for a JSON property

Removing a JSON property

Multiple changes

Performance considerations

Indexes on computed columns

Full-text indexes

Summary

Stretch Database

Stretch DB architecture

Is this for you?

Using Data Migration Assistant

Limitations of using Stretch Database

Limitations that prevent you from enabling the Stretch DB features for a table

Table limitations

Column limitations

Limitations for Stretch-enabled tables

Use cases for Stretch Database

Archiving of historical data

Archiving of logging tables

Testing Azure SQL database

Enabling Stretch Database

Enabling Stretch Database at the database level

Enabling Stretch Database by using wizard

Enabling Stretch Database by using Transact-SQL

Enabling Stretch Database for a table

Enabling Stretch DB for a table by using wizard

Enabling Stretch Database for a table by using Transact-SQL

Filter predicate with sliding window

Querying stretch databases

Querying and updating remote data

SQL Server Stretch Database pricing

Stretch DB management and troubleshooting

Monitoring Stretch Databases

Pause and resume data migration

Disabling Stretch Database

Disable Stretch Database for tables by using SSMS

Disabling Stretch Database for tables using Transact-SQL

Disabling Stretch Database for a database

Backing up and restoring Stretch-enabled databases

Summary

Temporal Tables

What is temporal data?

Types of temporal tables

Allen's interval algebra

Temporal constraints

Temporal data in SQL Server before 2016

Optimizing temporal queries

Temporal features in SQL:2011

System-versioned temporal tables in SQL Server 2017

How temporal tables work in SQL Server 2017

Creating temporal tables

Period columns as hidden attributes

Converting non-temporal tables to temporal tables

Migrating an existing temporal solution to system-versioned tables

Altering temporal tables

Dropping temporal tables

Data manipulation in temporal tables

Inserting data in temporal tables

Updating data in temporal tables

Deleting data in temporal tables

Querying temporal data in SQL Server 2017

Retrieving temporal data at a specific point in time

Retrieving temporal data from a specific period

Retrieving all temporal data

Performance and storage considerations with temporal tables

History retention policy in SQL Server 2017

Configuring the retention policy at the database level

Configuring the retention policy at the table level

Custom history data retention

History table implementation

History table overhead

Temporal tables with memory-optimized tables

What is missing in SQL Server 2017?

SQL Server 2016 and 2017 temporal tables and data warehouses

Summary

Tightening Security

SQL Server security basics

Defining principals and securables

Managing schemas

Object and statement permissions

Encrypting the data

Leveraging SQL Server data encryption options

Always Encrypted

Row-Level Security

Using programmable objects to maintain security

Predicate-based Row-Level Security

Exploring dynamic data masking

Defining masked columns

Dynamic data masking limitations

Summary

Query Store

Why Query Store?

What is Query Store?

Query Store architecture

Enabling and configuring Query Store

Enabling Query Store with SSMS

Enabling Query Store with Transact-SQL

Configuring Query Store

Query Store default configuration

Query Store recommended configuration

Disabling and cleaning Query Store

Query Store in action

Capturing the Query info

Capturing plan info

Collecting runtime statistics

Query Store and migration

Query Store – identifying regressed queries

Query Store – fixing regressed queries

Query Store reports in SQL Server Management Studio

Regressed queries

Top resource – consuming queries

Overall Resource Consumption report

Queries With Forced Plans

Queries With High Variation

Automatic tuning in SQL Server 2017

Regressed queries in the sys.dm_db_tuning_recommendations view

Automatic tuning

Capturing waits by Query Store in SQL Server 2017

Catalog view sys.query_store_wait_stats

Query Store use cases

SQL Server version upgrades and patching

Application and service releases, patching, failovers, and cumulative updates

Identifying ad hoc queries

Identifying unfinished queries

Summary

Columnstore Indexes

Analytical queries in SQL Server

Joins and indexes

Benefits of clustered indexes

Leveraging table partitioning

Nonclustered indexes in analytical scenarios

Using indexed views

Data compression and query techniques

Writing efficient queries

Columnar storage and batch processing

Columnar storage and compression

Recreating rows from columnar storage

Columnar storage creation process

Development of columnar storage in SQL Server

Batch processing

Nonclustered columnstore indexes

Compression and query performance

Testing the nonclustered columnstore index

Operational analytics

Clustered columnstore indexes

Compression and query performance

Testing the clustered columnstore index

Using archive compression

Adding B-tree indexes and constraints

Updating a clustered columnstore index

Deleting from a clustered columnstore index

Summary

Introducing SQL Server In-Memory OLTP

In-Memory OLTP architecture

Row and index storage

Row structure

Row header

Row payload

Index structure

Non-clustered index

Hash indexes

Creating memory-optimized tables and indexes

Laying the foundation

Creating a table

Querying and data manipulation

Performance comparisons

Natively compiled stored procedures

Looking behind the curtain of concurrency

Data durability concerns

Database startup and recovery

Management of In-Memory objects

Dynamic management objects

Extended events

PerfMon counters

Assistance in migrating to In-Memory OLTP

Summary

In-Memory OLTP Improvements in SQL Server 2017

Ch-Ch-Changes

Feature improvements

Collations

Computed columns for greater performance

Types of data

What's new with indexes?

Unconstrained integrity

Not all operators are created equal

Size is everything!

Improvements in the In-Memory OLTP engine

Down the index rabbit-hole

Large object support

Storage differences of on-row and off-row data

Cross-feature support

Security

Programmability

High availability

Tools and wizards

Summary

Supporting R in SQL Server

Introducing R

Starting with R

R language basics

Manipulating data

Introducing data structures in R

Getting sorted with data management

Understanding data

Basic visualizations

Introductory statistics

SQL Server R Machine Learning Services

Discovering SQL Server R Machine Learning Services

Creating scalable solutions

Deploying R models

Summary

Data Exploration and Predictive Modeling with R

Intermediate statistics – associations

Exploring discrete variables

Finding associations between continuous variables

Continuous and discrete variables

Getting deeper into linear regression

Advanced analysis – undirected methods

Principal Components and Exploratory Factor Analysis

Finding groups with clustering

Advanced analysis – directed methods

Predicting with logistic regression

Classifying and predicting with decision trees

Advanced graphing

Introducing ggplot2

Advanced graphs with ggplot2

Summary

Introducing Python

Starting with Python

Installing machine learning services and client tools

A quick demo of Python's capabilities

Python language basics

Working with data

Using the NumPy data structures and methods

Organizing data with pandas

Data science with Python

Creating graphs

Performing advanced analytics

Using Python in SQL Server

Summary

Graph Database

Introduction to graph databases

What is a graph?

Graph theory in the real world

What is a graph database?

When should you use graph databases?

Graph databases market

Neo4j

Azure Cosmos DB

OrientDB

FlockDB

DSE Graph

Amazon Neptune

AllegroGraph

Graph features in SQL Server 2017

Node tables

Edge tables

The MATCH clause

Basic MATCH queries

Advanced MATCH queries

SQL Graph system functions

The OBJECT_ID_FROM_NODE_ID function

The GRAPH_ID_FROM_NODE_ID function

The NODE_ID_FROM_PARTS function

The OBJECT_ID_FROM_EDGE_ID function

The GRAPH_ID_FROM_EDGE_ID function

The EDGE_ID_FROM_PARTS function

SQL Graph limitations

General limitations

Validation issues in edge tables

Referencing a non-existing node

Duplicates in an edge table

Deleting parent records with children

Limitations of the MATCH clause

Summary

Containers and SQL on Linux

Containers

Installing the container service

Creating our first container

Data persistence with Docker

SQL Server on Linux

How SQL Server works on Linux

Limitations of SQL Server on Linux

Installing SQL Server on Linux

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think