Microsoft SQL Server 2019 Essential Training

Course details

Master the fundamentals of SQL Server 2019, the industry-leading database management platform from Microsoft. In this course, instructor Adam Wilbert equips beginners with the core skills they need to create and manage SQL Server databases. Adam begins with the basics, showing how to install SQL Server and log into a local database server. He then discusses how to create new databases, design tables, define relationships, write queries, and use stored procedures and built-in functions. Plus, he demonstrates how to perform crucial database administration tasks, such as backups and restores, security enhancements, and performance monitoring.

Learning objectives
Installing Management Studio
Enabling features with SQL Server Configuration Monitor
Creating a database
Creating tables with Transact-SQL (T-SQL)
Creating relationships between tables
Creating views
Creating user-defined scalar functions
Backing up and restoring SQL Server
Monitoring and management
Security

Concepts

Introduction

Harness the modern and innovative tools in SQL Server 2019
What you should know

Get Started

Core concepts
SQL Server 2019 editions
Install SQL Server Developer Edition
Install Management Studio
Enable features with Configuration Manager

Log into the Server

Management Studio first steps
Authentication and user permissions
Create a new user account

Create Databases

Create the first database
Database configuration options
Table structures
Create a simple table
Edit a table's structure
Enter data into a table using the GUI
Import data from CSV files
Create tables from flat files
Install a sample database

Table Design for Healthy Databases

Understand data types
Table column properties
Primary key fields
Establish a default value
Check constraints
Unique constraints
Foreign keys
Create a foreign key column
Create relationships between tables

Structured Query Language

Understand the role of T-SQL
Use the T-SQL editor
Create a table with T-SQL
Add data to a table
Retrieve records with SELECT
Filter returned results with WHERE
Sort records
Delete records from a table
Update records in a table
Join related tables
LEFT JOIN and RIGHT JOIN
Remove a table from the database

Query Performance

Create a view of the data
Data table indexes
Create additional indexes on a table
Columnstore indexes
View execution plans
SQL Server's Query Store
Rebuild indexes

Query Programmability

Aggregate functions
Use built-in functions
Leverage user-defined scalar functions
Introduction to stored procedures
Create stored procedures
Parameterized stored procedures

Backup and Restore

Create a full backup of the database
Create a differential backup
Restore a backup
Automate backups
Business continuity solutions

Security

User roles and permissions
Database schemas
Dynamic data masking
Always On data encryption

Monitoring and Management

Explore the role of system databases
Prevent excessive tempdb autogrowth
Review the SQL Server error log
Dynamic management views
Database Console Commands (DBCC)

Conclusion

Next steps