SQL Server 2017 Developer’s Guide 1st Edition by Dejan Sarka,William Durkin,Milos Radivojevic 9781786465344 1786465345

1. Introduction to SQL Server 2016

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

Programming

Transact SQL enhancements

JSON

In-Memory OLTP

SQL Server tools

Business intelligence

R in SQL Server

Release cycles

Summary

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

Using triggers

Data abstraction – views, functions, and stored procedures

Transactions and error handling

Error handling

Using transactions

Beyond relational

Defining locations and shapes with Spatial Data

CLR integration

XML support in SQL Server

Summary

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

SQL Server Data Tools

Tools for developing R code

RStudio IDE

R Tools for Visual Studio

Summary

4. Transact-SQL Enhancements

New and enhanced functions and expressions

STRING_SPLIT

STRING_ESCAPE

COMPRESS

DECOMPRESS

CURRENT_TRANSACTION_ID

SESSION_CONTEXT

DATEDIFF_BIG

AT TIME ZONE

HASHBYTES

JSON functions

Enhanced DML and DDL statements

The conditional DROP statement (DROP IF EXISTS)

CREATE OR ALTER

Online Alter Column

TRUNCATE TABLE

Maximum key size for nonclustered indexes

New query hints

NO_PERFORMANCE_SPOOL

MAX_GRANT_PERCENT

MIN_GRANT_PERCENT

Summary

5. 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 the JSON format

FOR JSON AUTO

FOR JSON PATH

FOR JSON additional options

Adding a root node to the JSON output

Including 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 2016

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

6. Stretch Database

Stretch Database architecture

Is this for you?

Using Data Migration Assistant

Limitations of using Stretch Database

Limitations that prevent you from enabling the Stretch DB feature 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 Database

Pause and resume data migration

Disable Stretch Database

Disable Stretch Database for tables by using SSMS

Disable Stretch Database for tables using Transact-SQL

Disable Stretch Database for a database

Backup and restore Stretch-enabled databases

Summary

7. Temporal Tables

What is temporal data?

Types of temporal table

Allen’s interval algebra

Temporal constraints

Temporal data in SQL Server before 2016

Optimizing temporal queries

Temporal features in SQL:2011

System-versioned tables in SQL Server 2016

How temporal tables work in SQL Server 2016

Creating temporal tables

Period columns as hidden attributes

Converting non-temporal to temporal tables

Migration existing temporal solution to system-versioned tables

Altering temporal tables

Dropping temporal tables

Data manipulation in temporal tables

Querying temporal data in SQL Server 2016

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 data retention

History table physical implementation

History table overhead

Temporal tables with memory-optimized tables

What is missing in SQL Server 2016?

SQL Server 2016 temporal tables and data warehouses

Summary

8. Tightening the 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

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

Overall resource consumption

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

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

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

12. In-Memory OLTP Improvements in SQL Server 2016

Ch-Ch-Changes

Feature improvements

Collations

Data types and 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

13. 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 the data

Basic visualizations

Introductory statistics

SQL Server R services

Discovering SQL Server R services

Creating scalable solutions

Deploying R models

Summary

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

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