SQL Server 2017 Developer's Guide

SQL Server 2017 Developer's Guide

by Dejan Sarka, Milos Radivojevic, William Durkin
Released March 2018
Publisher(s): Packt Publishing
ISBN: 9781788476195

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Build smarter and efficient database application systems for your organization with SQL Server 2017

About This Book

  • Build database applications by using the development features of SQL Server 2017
  • Work with temporal tables to get information stored in a table at any time
  • Use adaptive querying to enhance the performance of your queries

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. 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.

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 2017

In Detail

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.

Style and approach

This book is a detailed guide to mastering the development features offered by SQL Server 2017, with a unique learn-as-you-do approach. All the concepts are explained in a very easy-to-understand manner and are supplemented with examples to ensure that you—the developer—are able to take that next step in building more powerful, robust applications for your organization with ease.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. SQL Server 2017 Developer's Guide
  3. Dedication
  4. Packt Upsell
    1. Why subscribe?
    2. PacktPub.com
  5. Contributors
    1. About the authors
    2. About the reviewer
    3. Packt is searching for authors like you
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  7. Introduction to SQL Server 2017
    1. Security
      1. Row-Level Security
        1. Dynamic data masking
        2. Always Encrypted
      2. Engine features
        1. Query Store
        2. Live query statistics
        3. Stretch Database
        4. Database scoped configuration
        5. Temporal Tables
        6. Columnstore indexes
        7. Containers and SQL Server on Linux 
      3. Programming
        1. Transact-SQL enhancements
        2. JSON
        3. In-Memory OLTP
        4. SQL Server Tools
      4. Business intelligence
        1. R in SQL server
      5. Release cycles
    2. Summary
  8. Review of SQL Server Features for Developers
    1. The mighty Transact-SQL SELECT
      1. Core Transact-SQL SELECT statement elements
      2. Advanced SELECT techniques
    2. DDL, DML, and programmable objects
      1. Data definition language statements
      2. Data modification language statements
      3. Triggers
      4. Data abstraction—views, functions, and stored procedures
    3. Transactions and error handling
      1. Error handling
      2. Using transactions
    4. Beyond relational
      1. Spatial data
      2. CLR integration
      3. XML support in SQL Server
    5. Summary
  9. SQL Server Tools
    1. Installing and updating SQL Server Tools
    2. New SSMS features and enhancements
      1. Autosave open tabs
      2. Searchable options
      3. Enhanced scroll bar
      4. Execution plan comparison
      5. Live query statistics
      6. Importing flat file Wizard
      7. Vulnerability assessment
    3. SQL Server Data Tools
    4. Tools for developing R and Python code
      1. RStudio IDE
      2. R Tools for Visual Studio 2015
      3. Setting up Visual Studio 2017 for data science applications
    5. Summary
  10. Transact-SQL and Database Engine Enhancements
    1. New and enhanced functions and expressions
      1. Using STRING_SPLIT
      2. Using STRING_ESCAPE
      3. Using STRING_AGG
        1. Handling NULLs in the STRING_AGG function
        2. The WITHIN GROUP clause
      4. Using CONCAT_WS
      5. Using TRIM
      6. Using TRANSLATE
      7. Using COMPRESS
      8. Using DECOMPRESS
      9. Using CURRENT_TRANSACTION_ID
      10. Using SESSION_CONTEXT
      11. Using DATEDIFF_BIG
      12. Using AT TIME ZONE
      13. Using HASHBYTES
      14. Using JSON functions
    2. Enhanced DML and DDL statements
      1. The conditional DROP statement (DROP IF EXISTS)
      2. Using CREATE OR ALTER
      3. Resumable online index rebuild
      4. Online ALTER COLUMN
      5. Using TRUNCATE TABLE
      6. Maximum key size for nonclustered indexes
    3. New query hints
      1. Using NO_PERFORMANCE_SPOOL
      2. Using MAX_GRANT_PERCENT
      3. Using MIN_GRANT_PERCENT
    4. Adaptive query processing in SQL Server 2017
      1. Interleaved execution
      2. Batch mode adaptive memory grant feedback
      3. Batch mode adaptive joins
        1. Disabling adaptive batch mode joins
    5. Summary
  11. JSON Support in SQL Server
    1. Why JSON?
    2. What is JSON?
      1. Why is it popular?
      2. JSON versus XML
      3. JSON objects
        1. JSON object
        2. JSON array
        3. Primitive JSON data types
    3. JSON in SQL Server prior to SQL Server 2016
      1. JSON4SQL
      2. JSON.SQL
      3. Transact-SQL-based solution
    4. Retrieving SQL Server data in JSON format
      1. FOR JSON AUTO
      2. FOR JSON PATH
        1. FOR JSON additional options
          1. Add a root node to JSON output
          2. Include NULL values in the JSON output
          3. Formatting a JSON output as a single object
      3. Converting data types
      4. Escaping characters
    5. Converting JSON data in a tabular format
      1. OPENJSON with the default schema
        1. Processing data from a comma-separated list of values
        2. Returning the difference between two table rows
      2. OPENJSON with an explicit schema
      3. Import the JSON data from a file
    6. JSON storage in SQL Server 2017
    7. Validating JSON data
    8. Extracting values from a JSON text
      1. JSON_VALUE
      2. JSON_QUERY
    9. Modifying JSON data
      1. Adding a new JSON property
      2. Updating the value for a JSON property
      3. Removing a JSON property
      4. Multiple changes
    10. Performance considerations
      1. Indexes on computed columns
      2. Full-text indexes
    11. Summary
  12. Stretch Database
    1. Stretch DB architecture
      1. Is this for you?
        1. Using Data Migration Assistant
    2. Limitations of using Stretch Database
      1. Limitations that prevent you from enabling the Stretch DB features for a table
        1. Table limitations
        2. Column limitations
      2. Limitations for Stretch-enabled tables
    3. Use cases for Stretch Database
      1. Archiving of historical data
      2. Archiving of logging tables
      3. Testing Azure SQL database
    4. Enabling Stretch Database
      1. Enabling Stretch Database at the database level
        1. Enabling Stretch Database by using wizard
        2. Enabling Stretch Database by using Transact-SQL
      2. Enabling Stretch Database for a table
        1. Enabling Stretch DB for a table by using wizard
        2. Enabling Stretch Database for a table by using Transact-SQL
        3. Filter predicate with sliding window
    5. Querying stretch databases
      1. Querying and updating remote data
    6. SQL Server Stretch Database pricing
    7. Stretch DB management and troubleshooting
      1. Monitoring Stretch Databases
      2. Pause and resume data migration
      3. Disabling Stretch Database
        1. Disable Stretch Database for tables by using SSMS
        2. Disabling Stretch Database for tables using Transact-SQL
        3. Disabling Stretch Database for a database
      4. Backing up and restoring Stretch-enabled databases
    8. Summary
  13. Temporal Tables
    1. What is temporal data?
      1. Types of temporal tables
      2. Allen's interval algebra
      3. Temporal constraints
      4. Temporal data in SQL Server before 2016
      5. Optimizing temporal queries
      6. Temporal features in SQL:2011
    2. System-versioned temporal tables in SQL Server 2017
      1. How temporal tables work in SQL Server 2017
      2. Creating temporal tables
        1. Period columns as hidden attributes
        2. Converting non-temporal tables to temporal tables
          1. Migrating an existing temporal solution to system-versioned tables
      3. Altering temporal tables
      4. Dropping temporal tables
      5. Data manipulation in temporal tables
        1. Inserting data in temporal tables
        2. Updating data in temporal tables
        3. Deleting data in temporal tables
      6. Querying temporal data in SQL Server 2017
        1. Retrieving temporal data at a specific point in time
        2. Retrieving temporal data from a specific period
        3. Retrieving all temporal data
      7. Performance and storage considerations with temporal tables
        1. History retention policy in SQL Server 2017
          1. Configuring the retention policy at the database level
          2. Configuring the retention policy at the table level
        2. Custom history data retention
        3. History table implementation
        4. History table overhead
      8. Temporal tables with memory-optimized tables
    3. What is missing in SQL Server 2017?
      1. SQL Server 2016 and 2017 temporal tables and data warehouses
    4. Summary
  14. Tightening Security
    1. SQL Server security basics
      1. Defining principals and securables
      2. Managing schemas
      3. Object and statement permissions
    2. Encrypting the data
      1. Leveraging SQL Server data encryption options
      2. Always Encrypted
    3. Row-Level Security 
      1. Using programmable objects to maintain security
      2. Predicate-based Row-Level Security 
    4. Exploring dynamic data masking
      1. Defining masked columns
      2. Dynamic data masking limitations
    5. Summary
  15. Query Store
    1. Why Query Store?
    2. What is Query Store?
    3. Query Store architecture
    4. Enabling and configuring Query Store
      1. Enabling Query Store with SSMS
      2. Enabling Query Store with Transact-SQL
      3. Configuring Query Store
        1. Query Store default configuration
        2. Query Store recommended configuration
    5. Disabling and cleaning Query Store
    6. Query Store in action
      1. Capturing the Query info
      2. Capturing plan info
      3. Collecting runtime statistics
      4. Query Store and migration
        1. Query Store – identifying regressed queries
        2. Query Store – fixing regressed queries
    7. Query Store reports in SQL Server Management Studio
      1. Regressed queries
      2. Top resource – consuming queries
      3. Overall Resource Consumption report
      4. Queries With Forced Plans
      5. Queries With High Variation
    8. Automatic tuning in SQL Server 2017
      1. Regressed queries in the sys.dm_db_tuning_recommendations view
      2. Automatic tuning
    9. Capturing waits by Query Store in SQL Server 2017
      1. Catalog view sys.query_store_wait_stats
    10. Query Store use cases
      1. SQL Server version upgrades and patching
      2. Application and service releases, patching, failovers, and cumulative updates
      3. Identifying ad hoc queries
      4. Identifying unfinished queries
    11. Summary
  16. Columnstore Indexes
    1. Analytical queries in SQL Server
      1. Joins and indexes
        1. Benefits of clustered indexes
        2. Leveraging table partitioning
        3. Nonclustered indexes in analytical scenarios
        4. Using indexed views
      2. Data compression and query techniques
        1. Writing efficient queries
    2. Columnar storage and batch processing
      1. Columnar storage and compression
        1. Recreating rows from columnar storage
        2. Columnar storage creation process
        3. Development of columnar storage in SQL Server
      2. Batch processing
    3. Nonclustered columnstore indexes
      1. Compression and query performance
        1. Testing the nonclustered columnstore index
      2. Operational analytics
    4. Clustered columnstore indexes
      1. Compression and query performance
        1. Testing the clustered columnstore index
        2. Using archive compression
      2. Adding B-tree indexes and constraints
      3. Updating a clustered columnstore index
        1. Deleting from a clustered columnstore index
    5. Summary
  17. Introducing SQL Server In-Memory OLTP
    1. In-Memory OLTP architecture
      1. Row and index storage
        1. Row structure
        2. Row header
      2. Row payload
      3. Index structure
        1. Non-clustered index
        2. Hash indexes
    2. Creating memory-optimized tables and indexes
      1. Laying the foundation
      2. Creating a table
    3. Querying and data manipulation
      1. Performance comparisons
      2. Natively compiled stored procedures
      3. Looking behind the curtain of concurrency
    4. Data durability concerns
    5. Database startup and recovery
    6. Management of In-Memory objects
      1. Dynamic management objects
    7. Extended events
    8. PerfMon counters
      1. Assistance in migrating to In-Memory OLTP
    9. Summary
  18. In-Memory OLTP Improvements in SQL Server 2017
    1. Ch-Ch-Changes
    2. Feature improvements
      1. Collations
      2. Computed columns for greater performance
      3. Types of data
      4. What's new with indexes?
      5. Unconstrained integrity
      6. Not all operators are created equal
      7. Size is everything!
    3. Improvements in the In-Memory OLTP engine
      1. Down the index rabbit-hole
      2. Large object support
      3. Storage differences of on-row and off-row data
      4. Cross-feature support
      5. Security
      6. Programmability
      7. High availability
      8. Tools and wizards
    4. Summary
  19. Supporting R in SQL Server
    1. Introducing R
      1. Starting with R
      2. R language basics
    2. Manipulating data
      1. Introducing data structures in R
      2. Getting sorted with data management
    3. Understanding data
      1. Basic visualizations
      2. Introductory statistics
    4. SQL Server R Machine Learning Services
      1. Discovering SQL Server R Machine Learning Services
      2. Creating scalable solutions
      3. Deploying R models
    5. Summary
  20. Data Exploration and Predictive Modeling with R
    1. Intermediate statistics – associations
      1. Exploring discrete variables
      2. Finding associations between continuous variables
      3. Continuous and discrete variables
      4. Getting deeper into linear regression
    2. Advanced analysis – undirected methods
      1. Principal Components and Exploratory Factor Analysis
      2. Finding groups with clustering
    3. Advanced analysis – directed methods
      1. Predicting with logistic regression
      2. Classifying and predicting with decision trees
    4. Advanced graphing
      1. Introducing ggplot2
      2. Advanced graphs with ggplot2
    5. Summary
  21. Introducing Python
    1. Starting with Python
      1. Installing machine learning services and client tools
      2. A quick demo of Python's capabilities
      3. Python language basics
    2. Working with data
      1. Using the NumPy data structures and methods
      2. Organizing data with pandas
    3. Data science with Python
      1. Creating graphs
      2. Performing advanced analytics
      3. Using Python in SQL Server
    4. Summary
  22. Graph Database
    1. Introduction to graph databases
      1. What is a graph?
      2. Graph theory in the real world
      3. What is a graph database?
      4. When should you use graph databases?
      5. Graph databases market
        1. Neo4j
        2. Azure Cosmos DB
        3. OrientDB
        4. FlockDB
        5. DSE Graph
        6. Amazon Neptune
        7. AllegroGraph
    2. Graph features in SQL Server 2017
      1. Node tables
      2. Edge tables
      3. The MATCH clause
        1. Basic MATCH queries
        2. Advanced MATCH queries
      4. SQL Graph system functions
        1. The OBJECT_ID_FROM_NODE_ID function
        2. The GRAPH_ID_FROM_NODE_ID function
        3. The NODE_ID_FROM_PARTS function
        4. The OBJECT_ID_FROM_EDGE_ID function
        5. The GRAPH_ID_FROM_EDGE_ID function
        6. The EDGE_ID_FROM_PARTS function
    3. SQL Graph limitations
      1. General limitations
        1. Validation issues in edge tables
        2. Referencing a non-existing node
        3. Duplicates in an edge table
        4. Deleting parent records with children
      2. Limitations of the MATCH clause
    4. Summary
  23. Containers and SQL on Linux
    1. Containers
      1. Installing the container service
        1. Creating our first container
        2. Data persistence with Docker
    2. SQL Server on Linux
      1. How SQL Server works on Linux 
        1. Limitations of SQL Server on Linux
        2. Installing SQL Server on Linux
    3. Summary
  24. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: SQL Server 2017 Developer's Guide
  • Author(s): Dejan Sarka, Milos Radivojevic, William Durkin
  • Release date: March 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781788476195