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

0. Introduction

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

1. Get Started

03 - Core concepts
04 - SQL Server 2019 editions
05 - Install SQL Server Developer Edition
06 - Install Management Studio
07 - Enable features with Configuration Manager

2. Log into the Server

08 - Management Studio first steps
09 - Authentication and user permissions
10 - Create a new user account

3. Create Databases

11 - Create the first database
12 - Database configuration options
13 - Table structures
14 - Create a simple table
15 - Edit a table's structure
16 - Enter data into a table using the GUI
17 - Import data from CSV files
18 - Create tables from flat files
19 - Install a sample database

4. Table Design for Healthy Databases

20 - Understand data types
21 - Table column properties
22 - Primary key fields
23 - Establish a default value
24 - Check constraints
25 - Unique constraints
26 - Foreign keys
27 - Create a foreign key column
28 - Create relationships between tables

5. Structured Query Language

29 - Understand the role of T-SQL
30 - Use the T-SQL editor
31 - Create a table with T-SQL
32 - Add data to a table
33 - Retrieve records with SELECT
34 - Filter returned results with WHERE
35 - Sort records
36 - Delete records from a table
37 - Update records in a table
38 - Join related tables
39 - LEFT JOIN and RIGHT JOIN
40 - Remove a table from the database

6. Query Performance

41 - Create a view of the data
42 - Data table indexes
43 - Create additional indexes on a table
44 - Columnstore indexes
45 - View execution plans
46 - SQL Server's Query Store
47 - Rebuild indexes

7. Query Programmability

48 - Aggregate functions
49 - Use built-in functions
50 - Leverage user-defined scalar functions
51 - Introduction to stored procedures
52 - Create stored procedures
53 - Parameterized stored procedures

8. Backup and Restore

54 - Create a full backup of the database
55 - Create a differential backup
56 - Restore a backup
57 - Automate backups
58 - Business continuity solutions

9. Security

59 - User roles and permissions
60 - Database schemas
61 - Dynamic data masking
62 - Always On data encryption

10. Monitoring and Management

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

Conclusion

68 - Next steps