Developing SQL Databases (M20762)

Developing SQL Databases Course (M20762) Outline

This is a 5 day course, with a total of 18 modules. Each day consists of expert instructor led training and the Developing SQL Database exam (Exam 70-762) is included as part of the course price. Upon completing the course, delegates may progress onto the Developing SQL Databases exam.

Module 1: Introduction to Database Development

• Introduction to the SQL Server Platform

• SQL Server Database Development Tasks

Module 2: Designing and Implementing Tables

• Designing Tables

• Data Types

• Working with Schemas

• Creating and Altering Tables

• Lab: Designing and Implementing Tables

Module 3: Advanced Table Designs

• Partitioning Data

• Compressing Data

• Temporal Tables

• Lab: Using Advanced Table Designs

• Partitioning Data

• Compressing Data

Module 4: Ensuring Data Integrity through Constraints

• Enforcing Data Integrity

• Implementing Data Domain Integrity

• Implementing Entity and Referential Integrity

• Lab: Using Data Integrity Through Constraints

Module 5: Introduction to Indexes

• Core Indexing Concepts

• Data Types and Indexes

• Heaps, Clustered, and Nonclustered Indexes

• Single Column and Composite Indexes

• Lab: Implementing Indexes

Module 6: Designing Optimized Index Strategies

• Index Strategies

• Managing Indexes

• Execution Plans

• The Database Engine Tuning Advisor

• Query Store

• Lab: Optimizing Indexes

Module 7: Columnstore Indexes

• Introduction to Columnstore Indexes

• Creating Columnstore Indexes

• Working with Columnstore Indexes

• Lab: Using Columnstore Indexes

Module 8: Designing and Implementing Views

• Introduction to Views

• Creating and Managing Views

• Performance Considerations for Views

• Lab: Designing and Implementing Views

Module 9: Designing and Implementing Stored Procedures

• Introduction to Stored Procedures

• Working with Stored Procedures

• Implementing Parameterized Stored Procedures

• Controlling Execution Context

• Lab: Designing and Implementing Stored Procedures

Module 10: Designing and Implementing User-Defined Functions

• Overview of Functions

• Designing and Implementing Scalar Functions

• Designing and Implementing Table-Valued Functions

• Considerations for Implementing Functions

• Alternatives to Functions

• Lab: Designing and Implementing User-Defined Functions

Module 11: Responding to Data Manipulation via Triggers

• Designing DML Triggers

• Implementing DML Triggers

• Advanced Trigger Concepts

• Lab: Responding to Data Manipulation by Using Triggers

Module 12: Using In-Memory Tables

• Memory-Optimized Tables

• Natively Compiled Stored Procedures

• Lab: Using In-Memory Database Capabilities

Module 13: Implementing Managed Code in SQL Server

• Introduction to CLR Integration in SQL Server

• Implementing and Publishing CLR Assemblies

• Lab: Implementing Managed Code in SQL Server

Module 14: Storing and Querying XML Data in SQL Server

• Introduction to XML and XML Schemas

• Storing XML Data and Schemas in SQL Server

• Implementing the XML Data Type

• Using the Transact-SQL FOR XML Statement

• Getting Started with XQuery

• Shredding XML

• Lab: Storing and Querying XML Data in SQL Server

Module 15: Storing and Querying Spatial Data in SQL Server

• Introduction to Spatial Data

• Working with SQL Server Spatial Data Types

• Using Spatial Data in Applications

• Lab: Working with SQL Server Spatial Data

Module 16: Storing and Querying BLOBs and Text Documents in SQL Server

• Considerations for BLOB Data

• Working with FILESTREAM

• Using Full-Text Search

• Lab: Storing and Querying BLOBs and Text Documents in SQL Server

Module 17: SQL Server Concurrency

• Concurrency and Transactions

• Locking Internals

• Lab: SQL Server Concurrency

Module 18: Performance and Monitoring

• Extended Events

• Working with extended Events

• Live Query Statistics

• Optimize Database File Configuration

• Metrics

• Lab: Monitoring, Tracing, and Baselining