Book Image

SQL Server 2017 Developer???s Guide

Book Image

SQL Server 2017 Developer???s Guide

Overview of this book

Microsoft SQL Server 2017 is a milestone in Microsoft's data platform timeline, as it brings in the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. This book prepares you for advanced topics by starting with a quick introduction to SQL Server 2017's new features. Then, it introduces you to enhancements in the Transact-SQL language and new database engine capabilities before switching to a different technology: JSON support. You will take a look at the security enhancements and temporal tables. Furthermore, the book focuses on implementing advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. Toward 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 analysis. You'll also learn to integrate Python code into SQL Server and graph database implementations as well as the deployment options on Linux and SQL Server in containers for development and testing. By the end of this book, you will be armed to design efficient, high-performance database applications without any hassle.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2017 Developer???s Guide
Mar, 2018 | 816 pages
No titles found
Table of Contents (25 chapters)
Title Page
Title Page
Copyright and Credits
Copyright and Credits
Dedication
Dedication
Packt Upsell
Packt Upsell
Contributors
Contributors
Preface
Preface
Free Chapter
1
Introduction to SQL Server 2017
Introduction to SQL Server 2017
Security
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 and Python code
Summary
4
Transact-SQL and Database Engine Enhancements
Transact-SQL and Database Engine Enhancements
New and enhanced functions and expressions
Enhanced DML and DDL statements
New query hints
Adaptive query processing in SQL Server 2017
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 JSON format
Converting JSON data in a tabular format
JSON storage in SQL Server 2017
Validating JSON data
Extracting values from a JSON text
Modifying JSON data
Performance considerations
Summary
6
Stretch Database
Stretch Database
Stretch DB 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 temporal tables in SQL Server 2017
What is missing in SQL Server 2017?
Summary
8
Tightening Security
Tightening 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
Automatic tuning in SQL Server 2017
Capturing waits by Query Store in SQL Server 2017
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 2017
In-Memory OLTP Improvements in SQL Server 2017
Ch-Ch-Changes
Feature improvements
Improvements in the In-Memory OLTP engine
Summary
13
Supporting R in SQL Server
Supporting R in SQL Server
Introducing R
Manipulating data
Understanding data
SQL Server R Machine Learning Services
Summary
14
Data Exploration and Predictive Modeling with R
Data Exploration and Predictive Modeling with R
Intermediate statistics – associations
Advanced analysis – undirected methods
Advanced analysis – directed methods
Advanced graphing
Summary
15
Introducing Python
Introducing Python
Starting with Python
Working with data
Data science with Python
Summary
16
Graph Database
Graph Database
Introduction to graph databases
Graph features in SQL Server 2017
SQL Graph limitations
Summary
17
Containers and SQL on Linux
Containers and SQL on Linux
Containers
SQL Server on Linux
Summary
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
Index

Querying and data manipulation

Now that we have a memory-optimized table, the next logical step is to start querying the table and manipulating the data stored inside it.

We have two methods of interacting with these memory-optimized objects. Firstly, we can issue standard T-SQL queries and allow the SQL Server Query Optimizer to deal with accessing this new type of table. The second method is to use natively compiled stored procedures:

Overview of SQL Server engine illustrating Query Interop between In-Memory OLTP and normal OLTP

In the preceding figure, we can see a simplified diagram of a query that is either querying normal tables or memory-optimized tables. In the center of the diagram is a node titled Query Interop. This is a mechanism that is responsible for enabling normal interpreted T-SQL statements to access memory optimized tables. Please note that this is a one-way mechanism and that the natively compiled stored procedures are not able to access traditional objects, only memory...

Previous Section
End of Section 4
Next Section