DP-800 Preparation Details
Preparing for the DP-800 Developing AI-Enabled Database Solutions certification exam? Start here with a complete, objective-wise DP-800 study guide designed to help you pass faster.
This guide brings together official Microsoft documentation, key concepts, and curated resources for every DP-800 exam objective, making it ideal for both beginners and last-minute revision.
Looking for the best DP-800 preparation resources in one place? This page covers everything you need to get exam-ready with confidence.
If this helped you, share it with others preparing for the DP-800 certification exam.
Exam Voucher for DP-800 with 1 Retake
Get 40% OFF with the combo
DP-800 Copilot Materials
Design and develop database solutions (35–40%)
Design and implement database objects
Design and implement tables, including data types, size, columns, indexes, and column store indexes
CREATE TABLE (Transact-SQL) – SQL Server
SQL Server and Azure SQL index architecture and design guide
Columnstore indexes – Overview – SQL Server
Design and implement specialized tables, including in-memory, temporal, external, ledger, and graph
Getting Started with Temporal Tables – Azure SQL
Updatable ledger tables – SQL Server
System-Versioned Temporal Tables with Memory-Optimized Tables – SQL Server
Design and implement JSON columns and indexes
Store JSON Documents – SQL Server
Design and implement database constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT
Primary and foreign key constraints – SQL Server
Unique constraints and check constraints – SQL Server
table_constraint (Transact-SQL) – SQL Server
Design and implement SEQUENCES
CREATE INDEX (Transact-SQL) – SQL Server
Columnstore indexes – Design guidance – SQL Server
Design and implement partitioning for tables and indexes
Columnstore indexes in data warehousing – SQL Server
Implement programmability objects
Create views
User-Defined Functions – SQL Server
CREATE FUNCTION (Transact-SQL) – SQL Server
Create scalar functions
User-Defined Functions – SQL Server
Scalar UDF Inlining – SQL Server
Create table-valued functions
CREATE FUNCTION (Transact-SQL) – SQL Server
Use table-valued parameters – SQL Server
Create stored procedures
CREATE FUNCTION (Transact-SQL) – SQL Server
User-Defined Functions – SQL Server
Create triggers
User-Defined Functions – SQL Server
CREATE FUNCTION (Microsoft Fabric, Azure Synapse Analytics)
Write advanced T-SQL code
Write common table expressions (CTEs)
WITH common_table_expression (Transact-SQL) – SQL Server
Write advanced T-SQL code – Training
Write queries that include window functions
Write advanced T-SQL code – Training
JSON Functions (Transact-SQL) – SQL Server
Write queries that include JSON functions, such as JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_CONTAINS, OPENJSON, and JSON_VALUE
JSON Functions (Transact-SQL) – SQL Server
OPENJSON (Transact-SQL) – SQL Server
Validate, Query, and Change JSON Data with Built-In Functions – SQL Server
Write queries that include regular expressions
Regular Expressions Functions (Transact-SQL) – SQL Server
Write advanced T-SQL code – Training
Write queries that include fuzzy string matching functions, such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE
Fuzzy String Match – SQL Server
JARO_WINKLER_DISTANCE (Transact-SQL) – SQL Server
Write graph queries that use the MATCH operator
MATCH (SQL Graph) – SQL Server
Write advanced T-SQL code – Training
Write correlated queries
WITH common_table_expression (Transact-SQL) – SQL Server
Parse and Transform JSON Data with OPENJSON – SQL Server
Implement error handling
Write advanced T-SQL code – Training
Work with JSON Data in SQL Server
Design and implement SQL solutions by using AI-assisted tools
Interpret security impact of using AI-assisted tools
What Is GitHub Copilot in SQL Server Management Studio?
Microsoft Copilot in the SQL Database Workload Overview – Microsoft Fabric
Enable GitHub Copilot and Microsoft Copilot in Fabric
Get Started – GitHub Copilot in SQL Server Management Studio
Overview of GitHub Copilot Integration – MSSQL Extension for Visual Studio Code
Configure model and MCP tool options in a GitHub Copilot or Copilot in Fabric chat session
Quickstart: Use GitHub Copilot Agent Mode – MSSQL Extension for Visual Studio Code
What Is Copilot in SSMS – Copilot in SQL Server Management Studio
Create and configure GitHub Copilot instruction files
Overview of GitHub Copilot Integration – MSSQL Extension for Visual Studio Code
Get Started – GitHub Copilot in SQL Server Management Studio
Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse
Stdio transport for SQL MCP Server – SQL MCP Server
Data API Builder in Visual Studio Code with MSSQL – SQL Server
Secure, optimize, and deploy database solutions (35–40%)
Implement data security and compliance
Design and implement data encryption, including Always Encrypted and column-level encryption
Encrypt a Column of Data – SQL Server
Security Overview – Azure SQL Database
Design and implement Dynamic Data Masking
Dynamic Data Masking – SQL Server
Implement data security and compliance with SQL – Training
Design and implement Row-Level Security (RLS)
Row-Level Security – SQL Server
Row-Level Security in Fabric Data Warehousing – Microsoft Fabric
Implement Row-Level Security in Fabric Data Warehouse – Microsoft Fabric
Design and implement object-level permissions
Secure Your Fabric Data Warehouse – Microsoft Fabric
Implement data security and compliance with SQL – Training
Implement secure database access, including passwordless
Implement data security and compliance with SQL – Training
Security Overview – Azure SQL Database
Implement auditing
Security Overview – Azure SQL Database
Secure Your Fabric Data Warehouse – Microsoft Fabric
Secure model endpoints, including Managed Identity
Implement data security and compliance with SQL – Training
Secure GraphQL, REST, and MCP endpoints
Configuration schema – Runtime section – Data API builder
Optimize database performance
Recommend database configurations
Performance Tuning Guidance for Applications and Databases – Azure SQL Database
Detectable types of query performance bottlenecks – Azure SQL Database
Preserve data integrity and consistency by using transaction isolation levels and concurrency controls
Secure, optimize, and deploy database solutions – Training
Understand and Resolve Blocking Problems – Azure SQL Database
Evaluate query performance by using query execution plans, DMVs, Query Store, and Query Performance Insight
Monitor Performance Using DMVs – Azure SQL Database
Monitor Performance by Using the Query Store – SQL Server
Detectable types of query performance bottlenecks – Azure SQL Database
Identify and resolve query performance issues, including blocking and deadlocks
Understand and Resolve Blocking Problems – Azure SQL Database
Analyze and Prevent Deadlocks – Azure SQL Database
Implement CI/CD by using SQL Database Projects
Design and implement a testing strategy, including unit tests and integration tests
Implement CI/CD by using SQL Database Projects – Training
Secure, optimize, and deploy database solutions – Training
Create and manage reference/static data in source control
What Are SQL Database Projects? – SQL Server
Implement CI/CD by using SQL Database Projects – Training
Create, build, and validate database models by using SQL Database Projects, including SDK-style models
What Are SQL Database Projects? – SQL Server
Use SDK-style SQL projects with the SQL Database Projects extension
Database DevOps in SQL Server Management Studio
Configure source control for SQL Database Projects
Fabric SQL database source control integration – Microsoft Fabric
Create and Deploy a SQL Project – SQL Server
Manage branching, pull requests, and conflict resolution
Implement CI/CD by using SQL Database Projects – Training
Fabric SQL database source control integration – Microsoft Fabric
Implement secrets management
Configuration schema – Data API builder
Implement CI/CD by using SQL Database Projects – Training
Detect schema drift by using SQL Database Projects
What Are SQL Database Projects? – SQL Server
Implement CI/CD by using SQL Database Projects – Training
Update an SQL database project and deploy changes
Create and Deploy a SQL Project – SQL Server
Database DevOps in SQL Server Management Studio
Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners
Implement CI/CD by using SQL Database Projects – Training
Create and Deploy a SQL Project – SQL Server
Integrate SQL solutions with Azure services
Create configuration files for Data API builder (DAB)
Configuration schema – Data API builder
Initialize a config with the DAB CLI – Data API builder
Configure entities for REST and GraphQL, including data caching, pagination, searching, and filtering
Configuration schema – Entities section – Data API builder
Configuration schema – Runtime section – Data API builder
Configure REST or GraphQL endpoints
Data API builder documentation
How to call GraphQL endpoints – Data API builder
Expose database objects, stored procedures, and views, including GraphQL relationships
Database-specific features reference – Data API builder
Configuration schema – Entities section – Data API builder
Configure and implement DAB deployment
Create and Deploy a SQL Project – SQL Server
Data API Builder in Visual Studio Code with MSSQL – SQL Server
Recommend Azure Monitor configurations, including Application Insights and Log Analytics
Monitor Performance Using DMVs – Azure SQL Database
Secure, optimize, and deploy database solutions – Training
Handle changes by using CES, CDC, Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps
Secure, optimize, and deploy database solutions – Training
Implement AI capabilities in database solutions (25–30%)
Design and implement models and embeddings
Evaluate external models, including multimodal, multilanguage, sizes, and structured output
Intelligent Applications and AI – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Create and manage external models
CREATE EXTERNAL MODEL (Transact-SQL) – SQL Server
ALTER EXTERNAL MODEL (Transact-SQL) – SQL Server
Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry
Intelligent Applications and AI FAQ – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Identify which columns to include in embeddings
Intelligent Applications and AI – SQL Server
Multi-model capabilities – Azure SQL
Design and implement chunks for embeddings
AI_GENERATE_EMBEDDINGS (Transact-SQL) – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Generate embeddings
AI_GENERATE_EMBEDDINGS (Transact-SQL) – SQL Server
CREATE EXTERNAL MODEL (Transact-SQL) – SQL Server
Vector similarity search with Azure SQL & Azure OpenAI – Code Samples
Design and implement intelligent search
Choose from full-text, semantic vector, and hybrid search
Choose an Azure Service for Vector Search – Azure Architecture Center
Hybrid Search Overview – Azure AI Search
Implement full-text search
Intelligent Applications and AI – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Design for vector data, including vector data type, vector indexes, and size
Vector Search & Vector Index – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Identify when to use vector-related types and functions for semantic searching, including VECTOR_NORMALIZE, VECTOR_DISTANCE, VECTORPROPERTY, and VECTOR_SEARCH
Vector Search & Vector Index – SQL Server
VECTOR_SEARCH (Transact-SQL) – SQL Server
Choose between using ANN and ENN for vector search
Vector Search & Vector Index – SQL Server
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Evaluate vector index types and metrics
Vector Search & Vector Index – SQL Server
Choose an Azure Service for Vector Search – Azure Architecture Center
Implement vector search
VECTOR_SEARCH (Transact-SQL) – SQL Server
Vector Search & Vector Index – SQL Server
Vector similarity search with Azure SQL & Azure OpenAI – Code Samples
Implement hybrid search
Microsoft SQL Server Database Provider – Vector Search – EF Core
Hybrid Search Overview – Azure AI Search
Implement reciprocal rank fusion (RRF)
Microsoft SQL Server Database Provider – Vector Search – EF Core
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Evaluate performance of vector and hybrid search
Choose an Azure Service for Vector Search – Azure Architecture Center
Vector & Embeddings Frequently Asked Questions (FAQ) – SQL Server
Design and implement retrieval-augmented generation (RAG)
Identify use cases for RAG
Intelligent Applications and AI FAQ – SQL Server
Design and implement RAG with SQL – Training
Create a prompt by using the sp_invoke_external_rest_endpoint stored procedure
sp_invoke_external_rest_endpoint (Transact-SQL) – SQL Server
Call REST endpoints from Azure SQL database – Code Samples
Convert structured data to JSON for language model processing
Work with JSON Data in SQL Server
Work with JSON Data – Azure SQL Database
Send results to language model
sp_invoke_external_rest_endpoint (Transact-SQL) – SQL Server
Intelligent Applications and AI FAQ – SQL Server
Extract language model responses
Design and implement RAG with SQL – Training
Work with JSON Data in SQL Server
This brings us to the end of the DP-800 Developing AI-Enabled Database Solutions Study Guide.
What do you think? Let me know in the comments section if I have missed out on anything. Also, I love to hear from you about how your preparation is going on!
In case you are preparing for other Data Engineering certification exams, check out the Data Engineering section for those exams.
Follow Me to Receive Updates on the DP-800 Exam
Want to be notified as soon as I post? Subscribe to the RSS feed / leave your email address in the subscribe section. Share the article to your social networks with the links below so it can benefit others.
