Microsoft SQL Server 2019: A Beginner’s Guide

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