36,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Microsoft SQL Server 2017 is the next big step in the data platform history of Microsoft as it brings in the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. Compared to its predecessor, SQL Server 2017 has evolved into Machine Learning with R services for statistical analysis and Python packages for analytical processing. This book prepares you for more advanced topics by starting with a quick introduction to SQL Server 2017’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 enhancements in the Transact-SQL language and new database engine capabilities 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.
Furthermore, the book focuses on implementing advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. Towards 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 in SQL Server and graph database implementations along with deployment options on Linux and SQL Server in containers for development and testing.
By the end of this book, you will have the required information to design efficient, high-performance database applications without any hassle.
What you will learn Explore the new development features introduced in SQL Server 2017 Identify opportunities for In-Memory OLTP technology Use columnstore indexes to get storage and performance improvements Exchange JSON data between applications and SQL Server Use the new security features to encrypt or mask the data Control the access to the data on the row levels Discover the potential of R and Python integration Model complex relationships with the graph databases in SQL Server 2017Who this book is for
Database developers and solution architects looking to design efficient database applications using SQL Server 2017 will find this book very useful. In addition, this book will be valuable to advanced analysis practitioners and business intelligence developers. Database consultants dealing with performance tuning will get a lot of useful information from this book as well.
Some basic understanding of database concepts and T-SQL is required to get the best out of this book.
Dejan Sarka Dejan Sarka, MCT and SQL Server MVP located in Ljubljana, Slovenia, is an independent trainer and consultant who focuses on the development of database and business intelligence applications. Besides his projects, he spends around half of his time training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan is the main author and co-author of many books and courses about databases and SQL Server. He is a frequent speaker at many worldwide events. Miloš Radivojević Miloš Radivojević is a database consultant in Vienna, Austria. He is a Data Platform MVP and specializes in SQL Server for application developers and performance/ query tuning. Currently, he works as a principal database consultant in bwin (GVC Holdings)—the largest regulated online gaming company in the world. Miloš is a cofounder of PASS Austria. He is also a speaker at international conferences and speaks regularly at SQL Saturday events and PASS Austria meetings. William Durkin William Durkin Data Platform Architect for Data Masterminds. He uses his decade of experience with SQL Server to help multinational corporations achieve their data management goals. Born in the UK and now based in Germany, William is a regular speaker at conferences around the globe, a Data Platform MVP, and the founder of the popular SQLGrillen event.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 936
Veröffentlichungsjahr: 2018
Ähnliche
SQL Server 2017 Developer's Guide
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Amey VarangaonkarAcquisition Editor: Vinay ArgekarContent Development Editor: Snehal KolteTechnical Editor: Sayli NikaljeCopy Editor: Safis EditingProject Coordinator: Manthan PatelProofreader: Safis EditingIndexer: Rekha NairGraphics: Tania DuttaProduction Coordinator: Nilesh Mohite
First published: March 2018
Production reference: 1140318
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78847-619-5
www.packtpub.com
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the authors
Dejan Sarka, MCT and Data Platform MVP, is an independent trainer and consultant focusing on the development of database and business intelligence applications. Besides working on projects, he spends about half of his time training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. He is the main author or co-author of 16 books on databases and SQL Server. He has also developed many courses and seminars for Microsoft, SolidQ, and Pluralsight.
Miloš Radivojević is a database consultant in Vienna, Austria. He is a Data Platform MVP and specializes in SQL Server for application developers and performance/query tuning. He works as a principal database consultant in bwin (GVC Holdings). He is a cofounder of PASS Austria and speaks regularly at international conferences.
William Durkin is the co-founder of Data Masterminds, a Microsoft Data Platform consultancy. William is a UK born Data Platform MVP, now living in Germany. William speaks at SQL Server focused events across the globe and is the founder and organizer of the small, but popular, SQLGrillen.
About the reviewer
Tomaž Kaštrun is an SQL Server developer and data analyst with more than 15 years experience in business warehousing, development, ETL, database administration, and query tuning. He also has more than 15 years experience in data analysis, data mining, statistical research, and machine learning.
He is a Microsoft SQL Server MVP for Data Platform and has been working with Microsoft SQL Server since version 2000. He is a blogger, the author of many articles, the co-author of a statistical analysis book, a speaker at community and Microsoft events, and an avid coffee drinker.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
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
Preface
Microsoft SQL Server is developing faster than ever before in its almost 30-year history. The latest versions, SQL Server 2016 and 2017, bring with them many important new features. Some of these new features just extend or improve features that were introduced in the previous versions of SQL Server, and some of them open a completely new set of possibilities for a database developer.
This book prepares its readers for more advanced topics by starting with a quick introduction to SQL Server 2016 and 2017's new features and a recapitulation of the possibilities database developers already had in previous versions of SQL Server. It then goes on to, the new tools are introduced. The next part introduces small delights in the Transact-SQL language. The book then switches to a completely new technology inside SQL Server—JSON support. This is where the basic chapters end and the more complex chapters begin. Stretch Database, security enhancements, and temporal tables are medium-level topics. The latter chapters of the book cover advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. The next two chapters introduce R and R support in SQL Server, and show how to use the R language for data exploration and analysis beyond what a developer can achieve with Transact-SQL. Python language support is then introduced. The next chapter deals with new possibilities for using data structures called graphs in SQL Server 2017. The final chapter introduces SQL Server on Linux and in containers.
By reading this book, you will explore all of the new features added to SQL Server 2016 and 2017. You will become capable of identifying opportunities for using the In-Memory OLTP technology. You will also learn how to use columnstore indexes to get significant storage and performance improvements for analytical applications. You will also be able to extend database design using temporal tables. You will learn how to exchange JSON data between applications and SQL Server in a more efficient way. For very large tables with some historical data, you will be able to migrate the historical data transparently and securely to Microsoft Azure by using Stretch Database. You will tighten security using the new security features to encrypt data or to get more granular control over access to rows in a table. You will be able to tune workload performance more efficiently than ever with Query Store, and use SQL Server on Linux platforms and in containers. Finally, you will discover the potential of R and Python integration with SQL Server.
Who this book is for
Database developers and solution architects looking to design efficient database applications using SQL Server 2017 will find this book very useful. Some basic understanding of database concepts and T-SQL is required to get the best out of this book.
What this book covers
Chapter 1, Introduction to SQL Server 2017, very briefly covers the most important features and enhancements, not just those for developers. The chapter shows the whole picture and point readers in the direction of where things are moving.
Chapter 2, Review of SQL Server Features for Developers, brief recapitulates the features available for developers in previous versions of SQL Server and serves as a foundation for an explanation of the many new features in SQL Server 2016. Some best practices are covered as well.
Chapter 3, SQL Server Tools, helps you understand the changes in the release management of SQL Server tools and explores small and handy enhancements in SQL Server Management Studio (SSMS). It also introduces RStudio IDE, a very popular tool for developing R code, and briefly covers SQL Server Data Tools (SSDT), including the new R Tools for Visual Studio (RTVS), a plugin for Visual Studio, which enables you to develop R code in an IDE that is popular among developers using Microsoft products and languages. The chapter introduces Visual Studio 2017 and shows how it can be used it for data science applications with Python.
Chapter 4, Transact-SQL and Database Engine Enhancements, explores small Transact-SQL enhancements: new functions and syntax extensions, ALTER TABLE improvements for online operations, and new query hints for query tuning.
Chapter 5, JSON Support in SQL Server, explores the JSON support built into SQL Server. This support should make it easier for applications to exchange JSON data with SQL Server.
Chapter 6, Stretch Database, helps you understand how to migrate historical or less frequently/infrequently accessed data transparently and securely to Microsoft Azure using the Stretch Database (Stretch DB) feature.
Chapter 7, Temporal Tables, introduces support for system-versioned temporal tables based on the SQL:2011 standard. We explain how this is implemented in SQL Server and demonstrate some use cases for it (for example, a time-travel application).
Chapter 8, Tightening Security, introduces three new security features. With Always Encrypted, SQL Server finally enables full data encryption. Row-level security on the other hand, restricts which data in a table can be seen by a specific user. Dynamic data masking is a soft feature that limits the exposure of sensitive data by masking it to non-privileged users.
Chapter 9, Query Store, guides you through Query Store and helps you troubleshoot and fix performance problems related to execution plan changes.
Chapter 10, Columnstore Indexes, revises columnar storage and then explores the huge improvements relating to columnstore indexes in SQL Server 2016: updatable non-clustered columnstore indexes, columnstore indexes on in-memory tables, and many other new features for operational analytics.
Chapter 11, Introducing SQL Server In-Memory OLTP, describes a feature introduced in SQL Server 2014 that is still underused: the In-Memory database engine. This provides significant performance gains for OLTP workloads.
Chapter 12, In-Memory OLTP Improvements in SQL Server 2017, describes all the improvements to the In-Memory OLTP technology in SQL Server 2017. These improvements extend the number of potential use cases and allow implementation with less development effort and risk.
Chapter 13, Supporting R in SQL Server, introduces R Services and the R language. The chapter explains how SQL Server R Services combine the power and flexibility of the open source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization.
Chapter 14, Data Exploration and Predictive Modeling with R, in SQL Server, shows how you can use R for advanced data exploration and manipulation, statistical analysis, and predictive modeling. All this is way beyond what is possible when using the T-SQL language.
Chapter 15, Introducing Python, teaches the Python basics and how to use the language inside SQL Server.
Chapter 16, Graph Databases, provides an overview of graph database architecture and how to create database objects in SQL Server 2017.
Chapter 17, Containers and SQL on Linux, introduces the two technologies mentioned in the chapter title and provides an overview of how to get started using them both.
To get the most out of this book
In order to run all of the demo code in this book, you will need SQL Server 2017 Developer or Enterprise Edition. In addition, you will extensively use SQL Server Management Studio.
You will also need the RStudio IDE and/or SQL Server Data Tools with R Tools for Visual Studio plug-in
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packtpub.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/SQLServer2017Developer’sGuide. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available athttps://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://www.packtpub.com/sites/default/files/downloads/https://github.com/PacktPublishing/SQLServer2017Developer’sGuide_ColorImages.pdf.
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
Introduction to SQL Server 2017
SQL Server is the main relational database management system product from Microsoft. It has been around in one form or another since the late 80s (developed in partnership with Sybase), but as a standalone Microsoft product, it's here since the early 90s. In the last 20 years, SQL Server has changed and evolved, gaining newer features and functionality along the way.
The SQL Server we know today is based on what was arguably the most significant (r)evolutionary step in its history: the release of SQL Server 2005. The changes that were introduced, allowed the versions that followed the 2005 release to take advantage of newer hardware and software improvements, such as: 64-bit memory architecture, better multi-CPU and multi-core support, better alignment with the .NET framework, and many more modernization's in general system architecture.
The incremental changes introduced in each subsequent version of SQL Server have continued to improve upon this solid new foundation. Fortunately, Microsoft has changed the release cycle for multiple products, including SQL Server, resulting in shorter time frames between releases. This has, in part, been due to Microsoft's focus on their much reported Mobile first, Cloud first strategy. This strategy, together with the development of the cloud version of SQL Server Azure SQL Database, has forced Microsoft into a drastically shorter release cycle. The advantage of this strategy is that we are no longer required to wait 3 to 5 years for a new release (and new features). There have been releases every 2 years since SQL Server 2012 was introduced, with multiple releases of Azure SQL Database in between the real versions.
While we can be pleased that we no longer need to wait for new releases, we are also at a distinct disadvantage. The rapid release of new versions and features leaves us developers with ever-decreasing periods of time to get to grips with the shiny new features. Prior versions had multiple years between releases, allowing us to build up a deeper knowledge and understanding of the available features, before having to consume new information.
Following on from the release of SQL Server 2016 was the release of SQL Server 2017, barely a year after 2016 was released. Many features were merely more polished/updated versions of the 2016 release, while there were some notable additions in the 2017 release.
In this chapter (and book), we will introduce what is new inside SQL Server 2017. Due to the short release cycle, we will outline features that are brand new in this release of the product and look at features that have been extended or improved upon since SQL Server 2016.
We will be outlining the new features in the following areas:
Security
Engine features
Programming
Business intelligence
Security
The last few years have made the importance of security in IT extremely apparent, particularly when we consider the repercussions of the Edward Snowden data leaks or multiple cases of data theft via hacking. While no system is completely impenetrable, we should always be considering how we can improve the security of the systems we build. These considerations are wide ranging and sometimes even dictated via rules, regulations, and laws. Microsoft has responded to the increased focus on security by delivering new features to assist developers and DBAs in their search for more secure systems.
Row-Level Security
The first technology that was introduced in SQL Server 2016 to address the need for increased/improved security is Row-Level Security (RLS). RLS provides the ability to control access to rows in a table based on the user executing a query. With RLS it is possible to implement a filtering mechanism on any table in a database, completely transparently to any external application or direct T-SQL access. The ability to implement such filtering without having to redesign a data access layer allows system administrators to control access to data at an even more granular level than before. The fact that this control can be achieved without any application logic redesign makes this feature potentially even more attractive to certain use-cases. RLS also makes it possible, in conjunction with the necessary auditing features, to lock down a SQL Server database so that even the traditional god-mode sysadmin cannot access the underlying data.
Dynamic data masking
The second security feature that we will be covering is Dynamic Data Masking (DDM). DDM allows the system administrator to define column level data masking algorithms that prevent users from reading the contents of columns, while still being able to query the rows themselves. This feature was initially aimed at allowing developers to work with a copy of production data without having the ability to actually see the underlying data. This can be particularly useful in environments where data protection laws are enforced (for example, credit card processing systems and medical record storage). Data masking occurs only at query runtime and does not affect the stored data of a table. This means that it is possible to mask a multi-terabyte database through a simple DDL statement, rather than resorting to the previous solution of physically masking the underlying data in the table we want to mask. The current implementation of DDM provides the ability to define a fixed set of functions to columns of a table, which will mask data when a masked table is queried. If a user has the permission to view the masked data, then the masking functions are not run, whereas a user who may not see masked data will be provided with the data as seen through the defined masking functions.
Always Encrypted
The third major security feature to be introduced in SQL Server 2016 is Always Encrypted. Encryption with SQL Server was previously a (mainly) server-based solution. Databases were either protected with encryption at the database level (the entire database was encrypted) or at the column level (single columns had an encryption algorithm defined). While this encryption was/is fully functional and safe, crucial portions of the encryption process (for example, encryption certificates) are stored inside SQL Server. This effectively gave the owner of a SQL Server instance the ability to potentially gain access to this encrypted data—if not directly, there was at least an increased surface area for a potential malicious access attempt. As ever more companies moved into hosted service and cloud solutions (for example, Microsoft Azure), the previous encryption solutions no longer provided the required level of control/security. Always Encrypted was designed to bridge this security gap by removing the ability of an instance owner to gain access to the encryption components. The entirety of the encryption process was moved outside of SQL Server and resides on the client side. While a similar effect was possible using homebrew solutions, Always Encrypted provides a fully integrated encryption suite into both the .Net Framework and SQL Server. Whenever data is defined as requiring encryption, the data is encrypted within the .NET framework and only sent to SQL Server after encryption has occurred. This means that a malicious user (or even system administrator) will only ever be able to access encrypted information should they attempt to query data stored via Always Encrypted.
Microsoft has made some positive progress in this area of the product. While no system is completely safe and no single feature can provide an all-encompassing solution, all three features provide a further option in building up, or improving upon, any system's current security level. As mentioned for each feature, please visit the dedicated chapter (Chapter 8, Tightening Security) to explore how each feature functions and how they may be used in your environments.
Engine features
The Engine features section is traditionally the most important, or interesting, for most DBAs or system administrators when a new version of SQL Server is released. However, there are also numerous engine feature improvements that have tangential meanings for developers too. So, if you are a developer, don't skip this section—or you may miss some improvements that could save you some trouble later on!
Query Store
The Query Store is possibly the biggest new engine feature to come with the release of SQL Server 2016. DBAs and developers should be more than familiar with the situation of a query behaving reliably for a long period, which suddenly changed into a slow-running, resource-killing monster. Some readers may identify the cause of the issue as the phenomenon of parameter sniffing or similarly through stale statistics. Either way, when troubleshooting to find out why one unchanging query suddenly becomes slow, knowing the query execution plan(s) that SQL Server has created and used can be very helpful. A major issue when investigating these types of problems is the transient nature of query plans and their execution statistics. This is where Query Store comes into play; SQL Server collects and permanently stores information on query compilation and execution on a per-database basis. This information is then persisted inside each database that is being monitored by the Query Store functionality, allowing a DBA or developer to investigate performance issues after the fact. It is even possible to perform longer term query analysis, providing an insight into how query execution plans change over a longer time frame. This sort of insight was previously only possible via handwritten solutions or third-party monitoring solutions, which may still not allow the same insights as the Query Store does.
Live query statistics
When we are developing inside SQL Server, each developer creates a mental model of how data flows inside SQL Server. Microsoft has provided a multitude of ways to display this concept when working with query execution. The most obvious visual aid is the graphical execution plan. There are endless explanations in books, articles, and training seminars that attempt to make reading these graphical representations easier. Depending upon how your mind works, these descriptions can help or hinder your ability to understand the data flow concepts—fully blocking iterators, pipeline iterators, semi-blocking iterators, nested loop joins... the list goes on. When we look at an actual graphical execution plan, we are seeing a representation of how SQL Server processed a query: which data retrieval methods were used, which join types were chosen to join multiple data sets, what sorting was required, and so on. However, this is a representation after the query has completed execution. Live Query Statistics offers us the ability to observe during query execution and identify how, when, and where data moves through the query plan. This live representation is a huge improvement in making the concepts behind query execution clearer and is a great tool to allow developers to better design their query and index strategies to improve query performance.
Stretch Database
Microsoft has worked a lot in the past few years on their Mobile First, Cloud First strategy. We have seen a huge investment in their cloud offering, Azure, with the line between on-premises IT and cloud-based IT being continually blurred. The features being released in the newest products from Microsoft continue this approach and SQL Server is taking steps to bridge the divide between running SQL Server as a fully on-premises solution and storing/processing relational data in the cloud. One big step in achieving this approach is the new Stretch Database feature with SQL Server 2016. Stretch Database allows a DBA to categorize the data inside a database, defining which data is hot and which is cold. This categorization allows Stretch Database to then move the cold data out of the on-premises database and into Azure Cloud Storage. The segmentation of data remains transparent to any user/application that queries the data, which now resides in two different locations. The idea behind this technology is to reduce storage requirements for the on-premises system by offloading large amounts of archive data onto cheaper, slower storage in the cloud.
This reduction should then allow the smaller hot data to be placed on smaller capacity, higher performance storage. The magic of Stretch Database is the fact that this separation of data requires no changes at the application or database query level. This is a purely storage-level change, which means the potential ROI of segmenting a database is quite large.
Database scoped configuration
Many DBAs who support multiple third-party applications running on SQL Server can experience the difficulty of setting up their SQL Server instances per the application requirements or best practices. Many third-party applications have prerequisites that dictate how the actual instance of SQL Server must be configured. A common occurrence is a requirement of configuring the Max Degree of Parallelism to force only one CPU to be used for query execution. As this is an instance-wide setting, this can affect all other databases/applications in a multi-tenant SQL Server instance (which is generally the case). With Database Scoped Configuration in SQL Server 2016, several previously instance-level settings have been moved to a database level configuration option. This greatly improves multi-tenant SQL Server instances, as the decision of, for example, how many CPUs can be used for query execution can be made at the database-level, rather than for the entire instance. This will allow DBAs to host databases with differing CPU usage requirements on the same instance, rather than having to either impact the entire instance with a setting or be forced to run multiple instances of SQL Server and possibly incur higher licensing costs.
Temporal Tables
There are many instances where DBAs or developers are required to implement a change tracking solution, allowing future analysis or assessment of data changes for certain business entities. A readily accessible example is the change history on a customer account in a CRM system. The options for implementing such a change tracking system are varied and have strengths and weaknesses. One such implementation that has seen wide adoption is the use of triggers, to capture data changes and store historical values in an archive table. Regardless of the implementation chosen, it was often cumbersome to be able to develop and maintain these solutions.
One of the challenges was in being able to incorporate table structure changes in the table being tracked. It was equally challenging creating solutions to allow for querying both the base table and the archive table belonging to it. The intelligence of deciding whether to query the live and/or archive data can require some complex query logic.
With the advent of Temporal Tables, this entire process has been simplified for both developers and DBAs. It is now possible to activate this change tracking on a table and push changes into an archive table with a simple change on a table's structure. Querying the base table and including a temporal attribute to the query is also a simple T-SQL syntax addition. As such, it is now possible for a developer to submit temporal analysis queries, and SQL Server takes care of splitting the query between the live and archive data and returning the data in a single result set.
Columnstore indexes
Traditional data storage inside SQL Server has used the row-storage format, where the data for an entire row is stored together on the data pages inside the database. SQL Server 2012 introduced a new storage format: columnstore. This format pivots the data storage, combining the data from a single column and storing the data together on the data pages. This storage format provides the ability of massive compression of data; it's orders of magnitude better than traditional row storage. Initially only non-clustered columnstore indexes were possible. With SQL Server 2014, clustered columnstore indexes were introduced, expanding the usability of the feature greatly. Finally, with SQL Server 2016, updateable columnstore indexes and support for In-Memory columnstore indexes have been introduced. The potential performance improvements through these improvements are huge.
Containers and SQL Server on Linux
For the longest time, SQL Server has run solely on the Windows operating system. This was a major roadblock for adoption in traditionally Unix/Linux based companies that used alternative RDBM systems instead. Containers have been around in IT for over a decade and have made a major impression in the application development world. The ability to now host SQL Server in a container provides developers with the ability to adopt the development and deployment methodologies associated with containers into database development. A second major breakthrough (and surprise) around SQL Server 2017 was the announcement of SQL Server being ported to Linux. The IT world was shocked at this revelation and what it meant for the other RDBM systems on the market. There is practically no other system with the same feature-set and support network available at the same price point. As such, SQL Server on Linux will open a new market and allow for growth in previously unreachable areas of the IT world.
This concludes the section outlining the engine features. Through Microsoft's heavy move into cloud computing and their Azure offerings, they have had increased need to improve their internal systems for themselves. Microsoft has been famous for their dogfooding approach of using their own software to run their own business and Azure is arguably their largest foray into this area. The main improvements in the database engine have been fueled by the need to improve their own ability to continue offering Azure database solutions at a scale, and provide features to allow databases of differing sizes and loads to be hosted together.
Programming
Without programming, a SQL Server isn't very useful. The programming landscape of SQL Server has continued to improve to adopt newer technologies over the years. SQL Server 2017 is no exception in this area. There have been some long-awaited general improvements and also some rather revolutionary additions to the product that change the way SQL Server can be used in future projects. This section will outline what programming improvements have been included in SQL Server 2017.
Transact-SQL enhancements
The last major improvements in the T-SQL language allowed for better processing of running totals and other similar window functions. This was already a boon and allowed developers to replace arcane cursors with high performance T-SQL. These improvements are never enough for the most performance conscious developers among us, and as such there were still voices requesting further incorporation of the ANSI SQL standards into the T-SQL implementation.
Notable additions to the T-SQL syntax include the ability to finally split comma-separated strings using a single function call, STRING_SPLIT(), instead of the previous hacky implementations using loops or the Common Language Runtime (CLR).
The sensible opposing syntax for splitting strings is a function to aggregate values together, STRING_AGG(), which returns a set of values in a comma-separated string. This replaces similarly hacky solutions using the XML data type of one of a multitude of looping solutions.
Each improvement in the T-SQL language further extends the toolbox that we, as developers, possess to be able to manipulate data inside SQL Server. The ANSI SQL standards provide a solid basis to work from and further additions of these standards are always welcome.
JSON
It is quite common to meet developers outside of the Microsoft stack who look down on products from Redmond. Web developers in particular have been critical of the access to modern data exchange structures, or rather the lack of it. JSON has become the de facto data exchange method for the application development world. It is similar in structure to the previous cool-kid XML, but for reasons beyond the scope of this book, JSON has overtaken XML and is the expected payload for application and database communications. Microsoft has included JSON as a native data type in SQL Server 2016 and provided a set of functions to accompany the data type.
In-Memory OLTP
In-Memory OLTP (codename Hekaton) was introduced in SQL Server 2014. The promise of ultra-high performance data processing inside SQL Server was a major feature when SQL Server 2014 was released. As expected with version-1 features, there were a wide range of limitations in the initial release and this prevented many customers from being able to adopt the technology. With SQL Server 2017, a great number of these limitations have been either raised to a higher threshold or completely removed. In-Memory OLTP has received the required maturity and extension in feature set to make it viable for prime production deployment. Chapter 11, Introducing SQL Server In-Memory OLTP will show an introduction to In-Memory OLTP, explaining how the technology works under the hood and how the initial release of the feature works in SQL Server 2014. Chapter 12, In-Memory OLTP Improvements in SQL Server 2017 will build on top of the introduction and explain how the feature has matured and improved with the release of SQL Server 2016 and 2017.
SQL Server Tools
Accessing or managing data inside SQL Server and developing data solutions are two separate disciplines, each with their own specific focus on SQL Server. As such, Microsoft has created two different tools, each tailored towards the processes and facets of these disciplines.
SQL Server Management Studio (SSMS), as the name suggests, is the main management interface between DBAs/developers and SQL Server. The studio was originally released with SQL Server 2005 as a replacement and consolidation of the old Query Analyzer and Enterprise Manager tools. As with any non-revenue-generating software, SSMS only received minimal attention over the years, with limitations and missing tooling for many of the newer features in SQL Server. With SQL Server 2016, the focus of Microsoft has shifted and SSMS has been de-coupled from the release cycle of SQL Server itself. This decoupling allows both SSMS and SQL Server to be developed without having to wait for each other or for release windows. New releases of SSMS are created on top of more recent versions of Visual Studio, and have seen almost monthly update releases since SQL Server 2016 was released into the market.
SQL Server Data Tools (SSDT) is also an application based on the Visual Studio framework. SSDT is focused on the application/data development discipline. SSDT is much more closely aligned with Visual Studio in its structure and the features offered. This focus includes the ability to create entire database projects and solution files, easier integration into source control systems, the ability to connect projects into automated build processes, and generally offering a developer-centric development environment with a familiarity with Visual Studio. It is possible to design and create solutions in SSDT for SQL Server using the Relational Engine, Analysis Services, Integration Services, Reporting Services, and of course the Azure SQL database.
This concludes the overview of programming enhancements inside SQL Server 2016. The improvements outlined are all solid evolutionary steps in their respective areas. New features are very welcome and allow us to achieve more while requiring less effort on our side. The In-memory OLTP enhancements are especially positive, as they now expand on the groundwork laid down in the release of SQL Server 2014. Please read the respective chapters to gain deeper insight into how these enhancements can help you.
Business intelligence
Business intelligence is a huge area of IT and has been a cornerstone of the SQL Server product since at least SQL Server 2005. As the market and technologies in the business intelligence space improve, so must SQL Server. The advent of cloud-based data analysis systems as well as the recent buzz around big data are driving forces for all data platform providers, and Microsoft is no exception here. While there are multiple enhancements in the business intelligence portion of SQL Server 2016, we will be concentrating on the feature that has a wider audience than just data analysts: the R language in SQL Server.
R in SQL server
Data analytics has been the hottest topic in IT for the past few years, with new niches being crowned as the pinnacles of information science almost as fast as technology can progress. However, IT does have a few resolute classics that have stood the test of time and are still in widespread use. SQL (in its many permutations) is a language we are well aware of in the SQL Server world. Another such language is the succinctly titled R. The R language is a data mining, machine learning, and statistical analysis language that has existed since 1993. Many professionals such as data scientists, data analysts, or statisticians have been using the R language and tools that belong in that domain for a similarly long time. Microsoft has identified that although they may want all of the world's data inside SQL Server, this is just not feasible or sensible. External data sources and languages like R exist and they need to be accessible in an integrated manner.
For this to work, Microsoft made the decision to purchase Revolution Analytics (a commercial entity producing the forked Revolution R) in 2015 and was then able to integrate the language and server process into SQL Server 2016. This integration allows a normal T-SQL developer to interact with the extremely powerful R service in a native manner, and allows more advanced data analysis to be performed on their data.
Release cycles
Microsoft has made a few major public-facing changes in the past 5 years. These changes include a departure from longer release cycles in their main products and a transition towards subscription-based services (for example, Office 365 and Azure services). The ideas surrounding continuous delivery and agile software development have also shaped the way that Microsoft has been delivering on their flagship integrated development environment Visual Studio, with releases occurring approximately every six months. This change in philosophy is now flowing into the development cycle of SQL Server. Due to the similarly constant release cycle of the cloud-version of SQL Server (Azure SQL Database), there is a desire to keep both the cloud and on-premises versions of the product as close to each other as possible. As such, it is unsurprising to see that the previous release cycle of every three to 5 years is being replaced with much shorter intervals. A clear example of this is that SQL Server 2016 released to the market in June of 2016, with a Community Technology Preview (CTP) of SQL Server 2017 being released in November of 2016 and the Release To Market (RTM) of SQL Server 2017 happening in October 2017. The wave of technology progress stops for no one. This is very clearly true in the case of SQL Server!
Summary
In this introductory chapter, we saw a brief outline of what will be covered in this book. Each version of SQL Server has hundreds of improvements and enhancements, both through new features and through extensions on previous versions. The outlines for each chapter provide an insight into the main topics covered in this book, and allow you to identify which areas you may like to dive into and where to find them.
So let's get going with the rest of the book and see what SQL Server 2017 has to offer.
Review of SQL Server Features for Developers
Before delving into the new features in SQL Server 2016 and 2017, let's have a quick recapitulation of the SQL Server features for developers that are already available in the previous versions of SQL Server. Please note that this chapter is not a comprehensive development guide; covering all features would be out of the scope of this book. Recapitulating the most important features will help you remember what you already have in your development toolbox, and also understand the need for and the benefits of the new or improved features in SQL Server 2016 and 2017.
The recapitulation starts with the mighty T-SQL SELECT statement. Besides the basic clauses, advanced techniques such as window functions, common table expressions, and the APPLY operator are explained. Then you will pass quickly through creating and altering database objects, including tables and programmable objects, such as triggers, views, user-defined functions, and stored procedures. You will also review the data modification language statements. Of course, errors might appear, so you have to know how to handle them. In addition, data integrity rules might require that two or more statements are executed as an atomic, indivisible block. You can achieve this with the help of transactions.
The last section of this chapter deals with parts of the SQL Server Database Engine that is marketed with the common name Beyond Relational
