Book Image

SQL Server 2016 Developer's Guide

By : Miloš Radivojević, Dejan Sarka, William Durkin
Book Image

SQL Server 2016 Developer's Guide

By: Miloš Radivojević, Dejan Sarka, William Durkin

Overview of this book

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. This book introduces you to the new features of SQL Server 2016 that 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 Server - JSON support. We also take a look at the Stretch database, security enhancements, and temporal tables. The last chapters concentrate on implementing advanced topics, including Query Store, column store indexes, and In-Memory OLTP. You will finally be introduced to R and learn how to use the R language with Transact-SQL for data exploration and analysis. By the end of this book, you will have the required information to design efficient, high-performance database applications without any hassle.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2016 Developer's Guide
Miloš Radivojević | Dejan Sarka | William Durkin
Mar, 2017 | 616 pages
No titles found
Table of Contents (21 chapters)
SQL Server 2016 Developer's Guide
SQL Server 2016 Developer's Guide
Credits
Credits
About the Authors
About the Authors
About the Reviewer
About the Reviewer
www.PacktPub.com
www.PacktPub.com
Customer Feedback
Customer Feedback
Preface
Preface
Free Chapter
1
Introduction to SQL Server 2016
Introduction to SQL Server 2016
Security
Engine features
Programming
Business intelligence
Release cycles
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 code
Summary
4
Transact-SQL Enhancements
Transact-SQL Enhancements
New and enhanced functions and expressions
Enhanced DML and DDL statements
New query hints
Summary
5
JSON Support in SQL Server
JSON Support in SQL Server
Why JSON?
What is JSON?
JSON in SQL Server prior to SQL Server 2016
Retrieving SQL Server data in the JSON format
Converting JSON data in a tabular format
JSON storage in SQL Server 2016
Validating JSON data
Extracting values from a JSON text
Modifying JSON data
Performance considerations
Summary
6
Stretch Database
Stretch Database
Stretch Database 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 tables in SQL Server 2016
History table physical implementation
What is missing in SQL Server 2016?
Summary
8
Tightening the Security
Tightening the 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
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 2016
In-Memory OLTP Improvements in SQL Server 2016
Ch-Ch-Changes
Summary
13
Supporting R in SQL Server
Supporting R in SQL Server
Introducing R
Manipulating data
Understanding the data
SQL Server R services
Summary
14
Data Exploration and Predictive Modeling with R in SQL Server
Data Exploration and Predictive Modeling with R in SQL Server
Intermediate statistics - associations
Advanced analysis - undirected methods
Advanced analysis - directed methods
Advanced graphing
Summary

Query Store in action

In this section, you will see how Query Store collects information about queries and query plans and how it can identify and fix regressed queries. It will be demonstrated how Query Store supports and facilitates an upgrade to SQL Server 2016.

First, you will create a new database with a single table and populate it with two million rows. This database will simulate a database that is created and used in SQL Server 2012 and that you restored in SQL Server 2016 but left in the old compatibility mode. Use the following code to accomplish this task:

IF DB_ID('Mila') IS NULL CREATE DATABASE Mila; 
GO  
USE Mila; 
GO 
--help function GetNums created by Itzik Ben-Gan (http://tsql.solidq.com) 
CREATE OR ALTER FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE 
AS 
RETURN 
  WITH 
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1), 
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS...
Previous Section
End of Section 7
Next Section