Microsoft SQL Server 2019 Essential Training

Introduction

• Harness the modern and innovative tools in SQL Server 2019
• What you should know
• Explore the code challenge environment

1. Get Started

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

2. Log into the Server

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

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

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

5. Structured Query Language

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

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

7. Query Programmability

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

8. Backup and Restore

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

9. Security

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

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