Course Content

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

Module 3: Advanced Table Design

• Partitioning Data
• Compressing Data
• Temporal Tables

Module 4: Ensuring Data Integrity through Constraints

• Enforcing Data Integrity
• Implementing Data Domain Integrity
• Implementing Entity and Referential Integrity

Module 5: Introduction to Indexes

• Core Indexing Concepts
• Data Types and Indexes
• Heaps, Clustered, and Nonclustered Indexes
• Single Column and Composite Indexes

Module 6: Designing Optimised Index Strategies

• Index Strategies
• Managing Indexes
• Execution Plans
• The Database Engine Tuning Advisor
• Query Store

Module 7: Columnstore Indexes

• Introduction to Columnstore Indexes
• Creating Columnstore Indexes
• Working with Columnstore Indexes

Module 8: Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

Module 9: Designing and Implementing Stored Procedures

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

Module 10: Designing and Implementing User-Defined Function

• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Considerations for Implementing Functions
• Alternatives to Functions

Module 11: Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

Module 12: Using In-Memory Tables

• Memory-Optimized Tables
• Natively Compiled Stored Procedures

Module 13: Implementing Managed Code in SQL Server

• Introduction to CLR Integration in SQL Server
• Implementing and Publishing CLR Assemblies

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

Module 15: Storing and Querying Spatial in SQL Server

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

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

• Considerations for BLOB Data
• Working with FILESTREAM
• Using Full-Text Search

Module 17: SQL Server Concurrency

• Concurrency and Transactions
• Locking Internals

Module 18: Performance and Monitoring

• Extended Events
• Working with extended Events
• Live Query Statistics
• Optimize Database File Configuration
• Metrics