Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by William Durkin, Miloš Radivojević, Dejan Sarka
SQL Server 2016 Developer's Guide
SQL Server 2016 Developer's Guide
SQL Server 2016 Developer's Guide
Credits
About the Authors
About the Reviewer
www.PacktPub.com
Why subscribe?
Customer Feedback
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
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
Summary
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Table of Contents
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset