Microsoft SQL Server 2019: A Beginner's Guide, Seventh Edition, 7th Edition

Microsoft SQL Server 2019: A Beginner's Guide, Seventh Edition, 7th Edition

by Dusan Petkovic
Released January 2020
Publisher(s): McGraw-Hill
ISBN: 9781260458886

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

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

Get Up to Speed on Microsoft® SQL Server® 2019 Quickly and Easily

Start working with Microsoft SQL Server 2019 in no time with help from this thoroughly revised, practical resource. Filled with real-world examples and hands-on exercises, Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition starts by explaining fundamental relational database system concepts. From there, you’ll learn how to write Transact-SQL statements, execute simple and complex database queries, handle system administration and security, and use powerful analysis and reporting tools. New topics such as SQL and JSON support, graph databases, and support for machine learning with R and Python are also covered in this step-by-step tutorial.

    • Install, configure, and customize Microsoft SQL Server 2019
    • Create and modify database objects with Transact-SQL statements
    • Write stored procedures and user-defined functions
    • Handle backup and recovery, and automate administrative tasks
    • Tune your database system for optimal availability and reliability
    • Secure your system using authentication, encryption, and authorization
    • Work with SQL Server Analysis Services, Reporting Services, and other BI tools
    • Gain knowledge of relational storage, presentation, and retrieval of data stored in the JSON format
    • Manage graphs using SQL Server Graph Databases
    • Learn about machine learning support for R and Python

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. About the Author
  5. Acknowledgments
  6. Contents at a Glance
  7. Contents
  8. Introduction
  9. Part I Basic Concepts and Installation
    1. Chapter 1 Relational Database Systems: An Introduction
      1. Database Systems: An Overview
        1. Variety of User Interfaces
        2. Physical Data Independence
        3. Logical Data Independence
        4. Query Optimization
        5. Data Integrity
        6. Concurrency Control
        7. Backup and Recovery
        8. Database Security
      2. Relational Database Systems
        1. Working with the Book’s sample Database
        2. SQL: A Relational Database Language
      3. Database Design
        1. Normal Forms
        2. Entity-Relationship Model
      4. Syntax Conventions
      5. Summary
      6. Exercises
    2. Chapter 2 Planning the Installation and Installing SQL Server
      1. SQL Server Editions and Management Tools
        1. SQL Server Editions
        2. Management Tools
      2. Planning Phase: General Recommendations
        1. Which Operating System Will Be Used?
        2. Which SQL Server Components Should Be Installed?
        3. Where Will the Root Directory Be Stored?
        4. Should Multiple Instances of the Database Engine Be Used?
        5. Which Authentication Mode for the Database Engine Should Be Used?
      3. Installation of SQL Server on Windows or Ubuntu
        1. Planning the Installation on Windows
        2. Installing SQL Server on Windows
        3. Installing SQL Server Directly on Ubuntu
      4. Summary
    3. Chapter 3 Front-End Tools for the Database Engine
      1. SQL Server Management Studio
        1. Connecting to a Server
        2. Registered Servers
        3. Object Explorer
        4. Organizing and Navigating SQL Server Management Studio’s Panes
      2. Using SQL Server Management Studio with the Database Engine
        1. Administering Database Servers
        2. Managing Databases Using Object Explorer
      3. Authoring Activities Using SQL Server Management Studio
        1. Query Editor
        2. Solution Explorer
        3. Data Discovery and Classification
      4. Azure Data Studio
        1. Installation of Azure Data Studio
        2. Configuration
        3. Object Explorer
        4. Code Editor
        5. Database Dashboards and Customization
      5. SQL Server Management Studio vs. Azure Data Studio
      6. Summary
      7. Exercises
  10. Part II Transact-SQL Language
    1. Chapter 4 SQL Components
      1. SQL’s Basic Objects
        1. Literal Values
        2. Identifiers
        3. Delimiters
        4. Comments
        5. Reserved Keywords
      2. Data Types
        1. Numeric Data Types
        2. Character Data Types
        3. Temporal Data Types
        4. Miscellaneous Data Types
        5. Storage Options
      3. Transact-SQL Functions
        1. Aggregate Functions
        2. Scalar Functions
      4. Scalar Operators
        1. Global Variables
      5. NULL Values
      6. Summary
      7. Exercises
    2. Chapter 5 Data Definition Language
      1. Creating Database Objects
        1. Creation of a Database
        2. CREATE TABLE: A Basic Form
        3. CREATE TABLE and Declarative Integrity Constraints
        4. Referential Integrity
        5. Creating Other Database Objects
        6. Integrity Constraints and Domains
      2. Modifying Database Objects
        1. Altering a Database
        2. Altering a Table
      3. Removing Database Objects
      4. Summary
      5. Exercises
    3. Chapter 6 Queries
      1. SELECT Statement: Its Clauses and Functions
        1. WHERE Clause
        2. GROUP BY Clause
        3. Aggregate Functions
        4. HAVING Clause
        5. ORDER BY Clause
        6. SELECT Statement and IDENTITY Property
        7. CREATE SEQUENCE Statement
        8. Set Operators
        9. CASE Expressions
      2. Subqueries
        1. Subqueries and Comparison Operators
        2. Subqueries and the IN Operator
        3. Subqueries and ANY and ALL Operators
      3. Temporary Tables
      4. Join Operator
        1. Two Syntax Forms to Implement Joins
        2. Natural Join
        3. Cartesian Product
        4. Outer Join
        5. Further Forms of Join Operations
      5. Correlated Subqueries
        1. Subqueries and the EXISTS Function
        2. Should You Use Joins or Subqueries?
      6. Table Expressions
        1. Derived Tables
        2. Common Table Expressions
      7. Summary
      8. Exercises
    4. Chapter 7 Modification of a Table’s Contents
      1. INSERT Statement
        1. Inserting a Single Row
        2. Inserting Multiple Rows
        3. Table Value Constructors and INSERT
      2. UPDATE Statement
      3. DELETE Statement
      4. Other T-SQL Modification Statements and Clauses
        1. TRUNCATE TABLE Statement
        2. MERGE Statement
        3. The OUTPUT Clause
      5. Summary
      6. Exercises
    5. Chapter 8 Stored Procedures and User-Defined Functions
      1. Procedural Extensions
        1. Block of Statements
        2. IF Statement
        3. WHILE Statement
        4. Local Variables
        5. Miscellaneous Procedural Statements
        6. Exception Handling with TRY, CATCH, and THROW
      2. Stored Procedures
        1. Creation and Execution of Stored Procedures
      3. User-Defined Functions
        1. Types of User-Defined Functions
        2. Creation and Execution of User-Defined Functions
        3. Changing the Structure of UDFs
      4. Summary
      5. Exercises
    6. Chapter 9 System Catalog
      1. Introduction to the System Catalog
      2. General Interfaces
        1. Catalog Views
        2. Dynamic Management Views and Functions
        3. Information Schema
      3. Proprietary Interfaces
        1. System Stored Procedures
        2. System Functions
        3. Property Functions
      4. Summary
      5. Exercises
    7. Chapter 10 Indices
      1. Introduction to Indices
        1. Clustered Indices
        2. Nonclustered Indices
      2. Transact-SQL and Indices
        1. Creating Indices
        2. Editing Information Concerning Indices
        3. Altering Indices
        4. Creation of Resumable Online Indices
        5. Removing and Renaming Indices
      3. Guidelines for Creating and Using Indices
        1. Indices and Conditions in the WHERE Clause
        2. Indices and the Join Operator
        3. Covering Index
        4. Missing Indices
      4. Special Types of Indices
      5. Summary
      6. Exercises
    8. Chapter 11 Views
      1. DDL Statements and Views
        1. Creating a View
        2. Altering and Removing Views
      2. DML Statements and Views
        1. View Retrieval
        2. INSERT Statement and a View
        3. UPDATE Statement and a View
        4. DELETE Statement and a View
        5. Editing Information Concerning Views
      3. Summary
      4. Exercises
    9. Chapter 12 Security System of the Database Engine
      1. Encrypting Data
        1. Symmetric Keys
        2. Asymmetric Keys
        3. Certificates
        4. Editing Metadata Concerning User Keys
        5. Extensible Key Management
        6. Methods of Data Encryption
      2. Authentication
        1. Setting Up the Database System Security
      3. Schemas
        1. User–Schema Separation
        2. DDL Schema-Related Statements
        3. Default Database Schemas
      4. Database Security
        1. Managing Database Security Using SQL Server Management Studio
        2. Managing Database Security Using Transact-SQL Statements
      5. Roles
        1. Fixed Server Roles
        2. Fixed Database Roles
        3. Application Roles
        4. User-Defined Server Roles
        5. User-Defined Database Roles
      6. Authorization
        1. GRANT Statement
        2. DENY Statement
        3. REVOKE Statement
        4. Managing Permissions Using SQL Server Management Studio
        5. Managing Authorization and Authentication of Contained Databases
      7. Change Tracking
      8. Data Security and Views
      9. Summary
      10. Exercises
    10. Chapter 13 Concurrency Control
      1. Concurrency Models
      2. Transactions
        1. Properties of Transactions
        2. Transact-SQL Statements and Transactions
        3. Transaction Log
        4. Editing Information Concerning Transactions and Logs
      3. Locking
        1. Lock Modes
        2. Lock Granularity
        3. Lock Escalation
        4. Affecting Locks
        5. Displaying Lock Information
        6. Deadlock
      4. Isolation Levels
        1. Concurrency Problems
        2. The Database Engine and Isolation Levels
      5. Row Versioning
        1. Read Committed Snapshot Isolation
        2. Snapshot Isolation
      6. Summary
      7. Exercises
    11. Chapter 14 Triggers
      1. Introduction to Triggers
        1. Creating a DML Trigger
        2. Modifying a Trigger’s Structure
        3. Using deleted and inserted Virtual Tables
      2. Application Areas for DML Triggers
        1. AFTER Triggers
        2. INSTEAD OF Triggers
        3. First and Last Triggers
      3. DDL Triggers and Their Application Areas
        1. Database-Level Triggers
        2. Server-Level Triggers
      4. Summary
      5. Exercises
  11. Part III SQL Server: System Administration
    1. 15 System Environment of the Database Engine
      1. System Databases
        1. master Database
        2. model Database
        3. tempdb Database
        4. msdb Database
      2. Disk Storage
        1. Properties of Data Pages
        2. Types of Data Pages
        3. Editing Information Concerning Disk Storage
        4. Parallel Processing of Tasks
      3. Utilities
        1. bcp Utility
        2. sqlcmd Utility
        3. mssql-cli Utility
        4. sqlservr Utility
      4. DBCC Commands
        1. Validation Commands
        2. Performance Command
      5. Policy-Based Management
        1. Key Terms and Concepts
        2. Using Policy-Based Management
      6. Summary
      7. Exercises
    2. Chapter 16 Backup, Recovery, and System Availability
      1. Reasons for Data Loss
      2. Introduction to Backup Methods
        1. Full Database Backup
        2. Differential Backup
        3. Transaction Log Backup
        4. File or Filegroup Backup
      3. Performing Database Backup
        1. Backing Up Using Transact-SQL Statements
        2. Backing Up Using SQL Server Management Studio
        3. Determining Which Databases to Back Up
      4. Performing Database Recovery
        1. Automatic Recovery
        2. Manual Recovery
        3. Recovery Models
      5. System Availability
        1. Using a Standby Server
        2. Using RAID Technology
        3. Database Mirroring
        4. Failover Clustering
        5. Log Shipping
        6. AlwaysOn
        7. Comparison of High-Availability Components
      6. Maintenance Plan Wizard
      7. Summary
      8. Exercises
    3. Chapter 17 Automating System Administration Tasks
      1. Starting SQL Server Agent
      2. Creating Jobs and Operators
        1. Creating a Job and Its Steps
        2. Creating a Job Schedule
        3. Notifying Operators About the Job Status
        4. Viewing the Job History Log
      3. Alerts
        1. Error Messages
        2. SQL Server Agent Error Log
        3. Windows Application Log
        4. Defining Alerts to Handle Errors
      4. Summary
      5. Exercises
    4. Chapter 18 Data Replication
      1. Distributed Data and Methods for Distributing
      2. SQL Server Replication: An Overview
        1. Publishers, Distributors, and Subscribers
        2. Publications and Articles
        3. Agents
        4. The distribution Database
        5. Replication Types
        6. Replication Models
      3. Managing Replication
        1. Configuring the Distribution and Publication Servers
        2. Setting Up Publications
        3. Configuring Subscription Servers
      4. Summary
      5. Exercises
    5. Chapter 19 Query Optimizer
      1. Phases of Query Processing
      2. How Query Optimization Works
        1. Query Analysis
        2. Index Selection
        3. Join Order Selection
        4. Join Processing Techniques
        5. Plan Caching
      3. Tools for Editing the Optimizer Strategy
        1. SET Statement
        2. SQL Server Management Studio and Graphical Execution Plans
        3. Examples of Execution Plans
        4. Dynamic Management Views and Query Optimizer
      4. Optimizer Hints
        1. Why Use Optimizer Hints
        2. Types of Optimizer Hints
      5. Summary
      6. Exercises
    6. Chapter 20 Performance Tuning
      1. Factors That Affect Performance
        1. Database Applications and Performance
        2. The Database Engine and Performance
        3. System Resources and Performance
      2. Monitoring Performance
        1. Performance Monitor: An Overview
        2. Monitoring the CPU
        3. Monitoring Memory
        4. Monitoring the Disk System
        5. Monitoring the Network Interface
      3. Choosing the Right Tool for Monitoring
        1. SQL Server Profiler
        2. Database Engine Tuning Advisor
        3. Extended Events
      4. Other Performance Tools of the Database Engine
        1. Query Store
        2. Automatic Tuning
        3. Performance Data Collector
        4. Resource Governor
        5. Live Query Statistics
      5. Summary
      6. Exercises
    7. Chapter 21 In-Memory OLTP
      1. Memory-Optimized Tables
        1. Pinned Tables as Predecessor of Memory-Optimized Tables
        2. Creating a Memory-Optimized Filegroup
        3. Creating Memory-Optimized Tables
      2. Row and Index Storage
        1. Row Storage
        2. Index Storage
      3. In-Memory OLTP and Concurrency Control
        1. Logging Memory-Optimized Objects
        2. Optimistic Multiversion Concurrency Control
      4. Accessing the Content of Memory-Optimized Tables
        1. Interpreted Transact-SQL
        2. Compiled Stored Procedures
      5. Editing Information Concerning In-Memory Objects
        1. Property Functions
        2. Catalog Views and System Stored Procedures
        3. Dynamic Management Views
      6. Tools for In-Memory OLTP
        1. Memory Management for In-Memory OLTP: Overview
        2. Memory Management Tools
        3. Migration Tools for In-Memory OLTP
      7. Summary
  12. Part IV SQL Server and Business Intelligence
    1. Chapter 22 Business Intelligence: An Introduction
      1. Online Transaction Processing vs. Business Intelligence
        1. Online Transaction Processing
        2. Business Intelligence Systems
      2. Data Warehouses and Data Marts
      3. Data Warehouse Design
      4. Cubes and Their Architectures
        1. Aggregation
        2. Physical Storage of a Cube
      5. Data Access
      6. Summary
      7. Exercises
    2. Chapter 23 SQL Server Analysis Services
      1. Multidimensional Model
        1. Multidimensional Model Terminology
        2. Creating a New Project Using Visual Studio 2019
        3. Creating and Processing of a Multidimensional Cube
        4. Delivering Data from the Multidimensional Model
      2. Tabular Model
        1. Workspace Database
        2. Creating a Tabular Model Solution
        3. A Tabular Model Example
        4. Delivering Data from the Tabular Project
      3. Multidimensional Model vs. Tabular Model
      4. Analysis Services: Data Security
      5. Summary
      6. Exercises
    3. Chapter 24 Business Intelligence and Transact-SQL
      1. Window Construct
        1. Partitioning
        2. Ordering and Framing
      2. Extensions of GROUP BY
        1. CUBE Operator
        2. ROLLUP Operator
        3. Grouping Functions
        4. Grouping Sets
      3. OLAP Query Functions
        1. Ranking Functions
        2. Statistical Aggregate Functions
      4. Standard and Nonstandard Analytic Functions
        1. TOP Clause
        2. OFFSET/FETCH
        3. NTILE Function
        4. Pivoting Data
        5. STRING_AGG Function
        6. APPROX_COUNT_DISTINCT Function
      5. Summary
      6. Exercises
    4. Chapter 25 SQL Server Reporting Services
      1. Reports: An Introduction
      2. SQL Server Reporting Services Architecture
        1. Reporting Services Windows Service
        2. Report Catalog
      3. Installation and Configuration of Reporting Services
      4. Creating Reports
        1. Installation of SQL Server Data Tools
        2. Creating Your First Report
        3. Creating a Parameterized Report
        4. Creating a Report Using a Chart
      5. Managing and Tuning Reports
        1. Reporting Services Web Portal
        2. Performance Issues
      6. Reporting Services Security
        1. Authentication
        2. Authorization
      7. Summary
      8. Exercises
    5. Chapter 26 Optimizing Techniques for Data Warehousing
      1. Data Partitioning
        1. How the Database Engine Partitions Data
        2. Steps for Creating Partitioned Tables
        3. Partitioning Techniques for Increasing System Performance
        4. Editing Information Concerning Partitioning
        5. Guidelines for Partitioning Tables and Indices
      2. Star Join Optimization
      3. Indexed Views
        1. Creating an Indexed View
        2. Modifying the Structure of an Indexed View
        3. Editing Information Concerning Indexed Views
        4. Benefits of Indexed Views
      4. Summary
    6. Chapter 27 Columnstore Indices
      1. Benefits of Columnstore Indices
      2. Internal Storage of Columnstore Indices
        1. Index Storage
        2. Compression
      3. Types of Columnstore Indices
        1. Clustered Columnstore Index
        2. Nonclustered Columnstore Index
      4. Editing Information Concerning Columnstore Indices
      5. Columnstore Indices: Performance
        1. Columnstore Indices vs. Rowstore Indices
        2. Batch Mode on Columnstore
      6. Summary
      7. Exercises
    7. Chapter 28 Intelligent Query Processing
      1. Adaptive Query Processing
        1. Memory Grant Feedback
        2. Adaptive Join
        3. Interleaved Execution
      2. Batch Mode on Rowstore
      3. Approximate Query Processing
      4. Scalar UDF Inlining
        1. Enabling and Disabling Scalar UDF Inlining
      5. Table Variable Deferred Compilation
      6. Summary
  13. Part V Beyond Relational Data
    1. Chapter 29 JSON Integration in the Database Engine
      1. An Introduction to JSON
        1. Why Support JSON in SQL Server?
      2. Storing JSON Documents in the Database Engine
      3. Presenting and Querying JSON Documents
        1. Presenting JSON Documents as Relational Data
        2. Presenting Relational Data as JSON Documents
        3. Querying JSON Documents
      4. Updating JSON Documents
      5. Summary
    2. Chapter 30 Spatial and Temporal Data
      1. Spatial Data
        1. Models for Representing Spatial Data
        2. GEOMETRY Data Type
        3. GEOGRAPHY Data Type
        4. GEOMETRY vs. GEOGRAPHY
        5. External Data Formats
      2. Working with Spatial Data Types
        1. Working with the GEOMETRY Data Type
        2. Working with the GEOGRAPHY Data Type
        3. Spatial Indices
        4. Editing Information Concerning Spatial Data
      3. Temporal Tables
        1. Creation of Temporal Tables
        2. Temporal Tables and DML Statements
        3. Querying Temporal Data
        4. Converting Nontemporal Tables into Temporal Tables
      4. Summary
    3. Chapter 31 SQL Server Graph Databases
      1. Graph Databases: A General Introduction
        1. Graph Databases: Models
        2. SQL Server Graph Databases: An Introduction
      2. Creating Node Tables and Edge Tables
        1. Creating Node Tables
        2. Creating Edge Tables
        3. Inserting Data into Edge Tables
      3. Querying Graph Data
        1. The MATCH Function
        2. Recursive Relationships
      4. Modifying and Editing Data in Graph Databases
        1. Deleting Graph Data
        2. Updating Graph Data
        3. Editing Information Concerning SQL Server Graph Databases
      5. Querying Graph Data Using Relational Queries
      6. Summary
      7. Exercises
  14. Part VI Machine Learning
    1. Chapter 32 SQL Server Machine Learning Services: R Support
      1. SQL Server R Services
        1. R Language: An Introduction
        2. Getting Started with R in SQL Server
        3. R Data Frames
      2. Data Visualization
        1. Data Visualization in R
        2. Integrate R in Power BI Desktop
      3. Predictive Modeling with R
        1. Solving Linear Regression Problems with R
      4. Summary
    2. Chapter 33 SQL Server Machine Learning Services: Python Support
      1. Python: An Introduction
        1. Getting Started with Python
        2. Python Data Frames
      2. Data Visualization with Python
      3. Predictive Modeling with Python in SQL Server
        1. Solving Linear Regression Problems Using Python
      4. Summary
  15. Appendix Exercise Solutions
  16. Index

Product information

  • Title: Microsoft SQL Server 2019: A Beginner's Guide, Seventh Edition, 7th Edition
  • Author(s): Dusan Petkovic
  • Release date: January 2020
  • Publisher(s): McGraw-Hill
  • ISBN: 9781260458886