Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Title Page
Copyright and Credits
SQL Server 2017 Developer's Guide
Dedication
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the authors
About the reviewer
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Reviews
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
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
Summary
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
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
Summary
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
Summary
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
Summary
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
Summary
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
Summary
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
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
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
Summary
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
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
In-Memory OLTP Improvements in SQL Server 2017
Ch-Ch-Changes
Feature improvements
Collations
Computed columns for greater performance
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
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 data
Basic visualizations
Introductory statistics
SQL Server R Machine Learning Services
Discovering SQL Server R Machine Learning Services
Creating scalable solutions
Deploying R models
Summary
Data Exploration and Predictive Modeling with R
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
Introducing Python
Starting with Python
Installing machine learning services and client tools
A quick demo of Python's capabilities
Python language basics
Working with data
Using the NumPy data structures and methods
Organizing data with pandas
Data science with Python
Creating graphs
Performing advanced analytics
Using Python in SQL Server
Summary
Graph Database
Introduction to graph databases
What is a graph?
Graph theory in the real world
What is a graph database?
When should you use graph databases?
Graph databases market
Neo4j
Azure Cosmos DB
OrientDB
FlockDB
DSE Graph
Amazon Neptune
AllegroGraph
Graph features in SQL Server 2017
Node tables
Edge tables
The MATCH clause
Basic MATCH queries
Advanced MATCH queries
SQL Graph system functions
The OBJECT_ID_FROM_NODE_ID function
The GRAPH_ID_FROM_NODE_ID function
The NODE_ID_FROM_PARTS function
The OBJECT_ID_FROM_EDGE_ID function
The GRAPH_ID_FROM_EDGE_ID function
The EDGE_ID_FROM_PARTS function
SQL Graph limitations
General limitations
Validation issues in edge tables
Referencing a non-existing node
Duplicates in an edge table
Deleting parent records with children
Limitations of the MATCH clause
Summary
Containers and SQL on Linux
Containers
Installing the container service
Creating our first container
Data persistence with Docker
SQL Server on Linux
How SQL Server works on Linux 
Limitations of SQL Server on Linux
Installing SQL Server on Linux
Summary
Other Books You May Enjoy
Leave a review - let other readers know what you think
← Prev
Back
Next →
← Prev
Back
Next →