- Home
- IT Courses
- MS-DP800T00: Develop AI-Enabled Database Solutions
MS-DP800T00: Develop AI-Enabled Database Solutions
Course Code: MS-DP800T00
This course provides students with the knowledge and skills to design and develop AI enabled database solutions across Microsoft SQL platforms, including SQL Server, Azure SQL, and SQL databases in Microsoft Fabric. It is intended for professionals who build modern data solutions that integrate structured and semi structured data and incorporate AI features into scalable enterprise applications. It will also be valuable for individuals who develop applications that rely on SQL based data services enhanced with vector search, embeddings, and other AI driven capabilities.
The audience for this course is data professionals who want to learn about designing and developing AI-enabled database solutions across Microsoft’s SQL platforms, including SQL Server, Azure SQL, and SQL databases in Microsoft Fabric. This role develops database solutions that include both structured and semi-structured data and integrates AI features into modern and highly scalable enterprise applications.
- Experience writing T-SQL queries.
- Basic understanding of database concepts such as tables, joins, and transactions
- Familiarity with SQL Server or Azure SQL Databases in Microsoft Fabric.
- Basic understanding of security concepts like authentication and authorization.
- Experience with Git version control and CI/CD concepts.
After completing this course, students will be able to:
- Create well-structured database objects and indexes
- Encapsulate business logic with stored procedures and functions
- Write advanced T-SQL using techniques such as Common Table Expressions (CTE), window functions, and error handling.
- Accelerate your development workflow with AI-assisted tools including GitHub Copilot and Fabric Copilot.
- Take your database solutions from development to production.
- Protect sensitive data with encryption, masking, and row-level security.
- Automate deployments with CI/CD pipelines using SQL Database Projects
This course will prepare delegates to write the Microsoft DP-800T00 Exam — Developing AI-Enabled Database Solutions Exam.
Modules
This module covers designing and implementing various database objects including tables with different data types, specialized table types, indexes, constraints, and partitioning strategies. You'll learn how to create and optimize database objects for modern SQL platforms.
Lessons:
- Introduction
- Understand your SQL Server-based platform choices
- Build effective tables
- Optimize with indexes
- Use specialized table types
- Enforce data integrity with constraints
- Manage JSON columns and indexes
- Partition tables for scale
- Exercise - Create and maintain database objects
- Module assessment
- Summary
After completing this module, students will be able to:
- Design and implement tables with appropriate data types, sizes, and columns
- Implement specialized tables including in-memory, temporal, external, LEDGER, and GRAPH tables
- Design and implement JSON columns and indexes
- Design and implement database constraints including primary key, foreign key, unique key, CHECK, and DEFAULT
- Design and implement SEQUENCES
- Design and implement partitioning strategies for tables and indexes
Learn how to create and use views, stored procedures, scalar functions, table-valued functions, and triggers to build maintainable, secure, and efficient database solutions.
Lessons:
- Introduction
- Create views
- Create stored procedures
- Create scalar functions
- Create table-valued functions
- Create triggers
- Choose when to use each option
- Exercise: Implement programmability objects in SQL Server
- Knowledge check
- Summary
After completing this module, students will be able to:
- Create views to simplify data access and provide security boundaries.
- Build stored procedures to encapsulate complex business logic and data modifications.
- Develop scalar functions to return single calculated values.
- Implement table-valued functions to return result sets for use in queries.
- Configure triggers to automatically respond to data modifications or database events.
- Choose the right programmability object based on specific requirements.
Learn advanced T-SQL techniques including CTEs, window functions, JSON, regular expressions, fuzzy matching, graph queries, and error handling for SQL Server, Azure SQL, and Fabric.
Lessons:
- Introduction
- Organize queries with Common Table Expressions
- Apply window functions for analytics
- Process JSON data with built-in functions
- Match patterns with regular expressions
- Find approximate matches with fuzzy string functions
- Traverse relationships with graph queries
- Compare rows with correlated subqueries
- Handle errors with TRY...CATCH
- Exercise - Work with JSON functions
- Module assessment
- Summary
After completing this module, students will be able to:
- Write Common Table Expressions (CTEs) including recursive CTEs for hierarchical data
- Use window functions for ranking, aggregation, and analytical calculations
- Parse, construct, and transform JSON data using JSON functions
- Apply regular expressions for pattern matching and text manipulation
- Find approximate matches using fuzzy string matching functions
- Create and query graph data using the MATCH operator
- Write correlated subqueries for row-by-row comparisons
- Implement structured error handling with TRY...CATCH
Learn how to protect sensitive data and meet compliance requirements by implementing encryption, masking, access controls, and auditing across Microsoft's SQL platforms.
Lessons:
- Introduction
- Protect data with encryption
- Configure dynamic data masking
- Implement row-level security
- Manage permissions and secure access
- Implement auditing
- Configure secure access to AI services
- Secure data API endpoints
- Exercise: Implement security features
- Module assessment
- Summary
After completing this module, you will be able to:
- Design and implement data encryption using Always Encrypted and column-level encryption
- Configure Dynamic Data Masking to protect sensitive data
- Implement Row-Level Security to filter data access based on user context
- Design object-level permissions and implement secure, passwordless database access using Microsoft Entra ID
- Configure auditing to track database activity
- Secure model endpoints and API endpoints for modern applications
Optimize Azure SQL Database performance by choosing the right service tier and managing concurrency with transaction isolation levels. Analyze queries with execution plans and DMVs. Use Query Store for plan management and diagnose blocking and deadlocks.
Lessons:
- Introduction
- Recommend database configurations
- Preserve data integrity with transaction isolation levels and concurrency controls
- Evaluate query performance with execution plans and DMVs
- Monitor and tune queries with Query Store and Query Performance Insight
- Identify and resolve blocking and deadlocks
- Exercise: Optimize query performance
- Knowledge check
- Summary
After completing this module, you will be able to:
- Evaluate and recommend database configurations including service tiers, compute tiers, and resource limits.
- Choose transaction isolation levels and concurrency controls that balance consistency with throughput.
- Analyze query performance using execution plans and dynamic management views.
- Monitor and tune queries with Query Store and Query Performance Insight.
- Identify and resolve blocking and deadlocks using DMVs and Extended Events.