SQL Server 2016 developer's guide : get the most out of the rich development capabilities of SQL Server 2016 to build efficient database applications for your organization / 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.

Databases.

Physical Description:

1 online resource (607 pages) : illustrations

Edition:

1st edition

Other Title:

Get the most out of the rich development capabilities of SQL Server 2016 to build efficient database applications for your organization

Place of Publication:

Birmingham, London ; Mumbai, [India] : Packt Publishing, 2017.

System Details:

Mode of access: World Wide Web.

text file

Biography/History:

Radivojevia Milo: Milo Radivojevi is a data platform MVP and specializes in SQL Server for application developers and performance/ query tuning. Milo is a co-founder of PASS Austria. Sarka Dejan: Dejan Sarka, MCT and Microsoft Data Platform MVP, is an independent trainer and consultant who focuses on the development of database and business intelligence applications. He is the founder of the Slovenian SQL Server and. NET Users Group. Durkin William: William Durkin is a data platform architect for Data Masterminds, he is a regular speaker at conferences around the globe, a Data Platform MVP, and the founder of the popular SQLGrillen event.

Summary:

Get the most out of the rich development capabilities of SQL Server 2016 to build efficient database applications for your organization About This Book Utilize the new enhancements in Transact-SQL and security features in SQL Server 2016 to build efficient database applications Work with temporal tables to get information about data stored in the table at any point in time A detailed guide to SQL Server 2016, introducing you to multiple new features and enhancements to improve your overall development experience Who This Book Is For This book is for database developers and solution architects who plan to use the new SQL Server 2016 features for developing efficient database applications. It is also ideal for experienced SQL Server developers who want to switch to SQL Server 2016 for its rich development capabilities. Some understanding of the basic database concepts and Transact-SQL language is assumed. What You Will Learn Explore the new development features introduced in SQL Server 2016 Identify opportunities for In-Memory OLTP technology, significantly enhanced in SQL Server 2016 Use columnstore indexes to get significant storage and performance improvements Extend database design solutions using temporal tables Exchange JSON data between applications and SQL Server in a more efficient way Migrate historical data transparently and securely to Microsoft Azure by using Stretch Database Use the new security features to encrypt or to have more granular control over access to rows in a table Simplify performance troubleshooting with Query Store Discover the potential of R’s integration with SQL Server In Detail 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. Compared to its predecessors, SQL Server 2016 offers developers a unique opportunity to leverage the advanced features and build applications that are robust, scalable, and easy to administer. This book introduces you to new features of SQL Server 2016 which 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 Ser...

Contents:

Cover

Credits

About the Authors

About the Reviewer

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 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

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

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

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

SQL Server Data Tools

Tools for developing R code

RStudio IDE

R Tools for Visual Studio

Chapter 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

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

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

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

Chapter 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

Chapter 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

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

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

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

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

Chapter 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

Programmability

High availability

Tools and wizards

Chapter 13: Supporting R in SQL Server

Introducing R

Starting with R

R language Basics.

Manipulating data.

Notes:

Includes index.

Includes bibliographical references and index.

Description based on online resource; title from PDF title page (ebrary, viewed April 11, 2017).

ISBN:

1-78646-074-2

OCLC:

980774492