SQL Server 2017 developer's guide : a professional guide to designing and developing enterprise database applications / Dejan Sarka, Milos Radivojevic, William Durkin.

Format:

Book

Author/Creator:

Sarka, Dejan, author.

Radivojevic, Milos, author.

Durkin, William, author.

Language:

English

Subjects (All):

SQL server.

Client/server computing.

Database management.

Physical Description:

1 online resource (816 pages)

Edition:

1st edition

Place of Publication:

Birmingham ; Mumbai : Packt, 2018.

System Details:

text file

Summary:

Build smarter and efficient database application systems for your organization with SQL Server 2017 About This Book Build database applications by using the development features of SQL Server 2017 Work with temporal tables to get information stored in a table at any time Use adaptive querying to enhance the performance of your queries Who This Book Is For Database developers and solution architects looking to design efficient database applications using SQL Server 2017 will find this book very useful. In addition, this book will be valuable to advanced analysis practitioners and business intelligence developers. Database consultants dealing with performance tuning will get a lot of useful information from this book as well. Some basic understanding of database concepts and T-SQL is required to get the best out of this book. What You Will Learn Explore the new development features introduced in SQL Server 2017 Identify opportunities for In-Memory OLTP technology Use columnstore indexes to get storage and performance improvements Exchange JSON data between applications and SQL Server Use the new security features to encrypt or mask the data Control the access to the data on the row levels Discover the potential of R and Python integration Model complex relationships with the graph databases in SQL Server 2017 In Detail Microsoft SQL Server 2017 is the next big step in the data platform history of Microsoft as it brings in the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. Compared to its predecessor, SQL Server 2017 has evolved into Machine Learning with R services for statistical analysis and Python packages for analytical processing. This book prepares you for more advanced topics by starting with a quick introduction to SQL Server 2017'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 enhancements in the Transact-SQL language and new database engine capabilities 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. Furthermore, the book focuses on implementing advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. Towards 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 a...

Contents:

Cover

Title Page

Copyright and Credits

Dedication

Packt Upsell

Contributors

Table of Contents

Preface

Chapter 1: 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

Chapter 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

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

Chapter 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

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

Chapter 4: 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

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

Chapter 6: 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

Chapter 7: 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

Chapter 8: 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

Using programmable objects to maintain security

Predicate-based Row-Level Security

Exploring dynamic data masking

Defining masked columns

Dynamic data masking limitations

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

Chapter 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

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

Chapter 11: Introducing SQL Server In-Memory OLTP

In-Memory OLTP architecture

Row and index storage

Row structure

Row header

Row payload.

Index structure.

Notes:

Description based on print version record.

OCLC:

1030042366