DP-800 Exam Domains Explained: Complete Breakdown of All 3 Topics

The Microsoft Certified: SQL AI Developer Associate credential, earned by passing Exam DP-800: Developing AI-Enabled Database Solutions, is one of the most consequential database certifications Microsoft has launched in years. It reframes the SQL professional as someone who not only writes T-SQL and designs schemas, but also actively builds AI capabilities — vector search, embeddings, RAG pipelines — directly into the database tier.

If you’re preparing for this exam, your single most important resource is the official skills measured document (last updated March 12, 2026). That document is organized into three weighted domains, and understanding what’s actually inside each one is the difference between passing comfortably and second-guessing yourself for two extra weeks.

After preparing teams for SQL and Azure data certifications for over a decade, I’ll walk you through each DP-800 domain the way I’d walk a colleague through it: what’s really being tested, the traps people fall into, and the study moves that pay off.

The Big Picture: How the Three Domains Fit Together

Before we dig into individual domains, here’s the structure:

Domain	Title	Weight
1	Design and develop database solutions	35–40%
2	Secure, optimize, and deploy database solutions	35–40%
3	Implement AI capabilities in database solutions	25–30%

The exam is 100 minutes long with roughly 40–60 questions drawn from multiple choice, multi-response, drag-and-drop, case studies, and interactive simulation-style items. The passing score is 700 out of 1000. (Study Guide for Exam DP-800: Developing AI-Enabled Database Solutions, 2026)

Notice that Domains 1 and 2 each carry up to 40% weight, while the headline-grabbing AI domain is the smallest of the three. This often surprises candidates. Microsoft’s signal is clear: you cannot pass DP-800 by being an AI enthusiast with weak T-SQL and no DevOps background. The AI capabilities sit on top of a solid SQL development and operational foundation.

DP-800 Exam 2026 Blueprint Guide: Everything You Need to Know About Developing AI-Enabled Database Solutions

Domain 1: Design and Develop Database Solutions (35–40%)

In-depth Breakdown

This is the largest single domain, and for good reason. It tests whether you can build the database layer that everything else in the exam — security, deployment, and AI features — depends on. Microsoft splits this domain into four sub-areas:

1. Design and implement database objects. You’re tested on tables (data types, sizing, columns, indexes, columnstore indexes), specialized table types (in-memory OLTP, temporal, external, ledger, graph), JSON columns and JSON indexes, constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT), SEQUENCES, and partitioning for both tables and indexes.

2. Implement programmability objects. Views, scalar functions, table-valued functions, stored procedures, and triggers. Microsoft expects you to know not only the syntax but also when each is appropriate.

3. Write advanced T-SQL code. This is where things get interesting. You’ll see common table expressions (CTEs), window functions, JSON functions (JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_CONTAINS, OPENJSON, JSON_VALUE), regular expressions (REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, REGEXP_SPLIT_TO_TABLE), fuzzy string matching (EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE), graph queries with the MATCH operator, correlated subqueries, and structured error handling.

4. Design and implement SQL solutions using AI-assisted tools. Configuring GitHub Copilot and Microsoft Copilot in Fabric, setting up Model Context Protocol (MCP) tool options in a Copilot session, creating Copilot instruction files, and connecting to MCP server endpoints (including Microsoft SQL Server and Fabric Lakehouse). You also need to interpret the security impact of using AI-assisted development tools.

Key Concepts

The concepts that come up most consistently:

• Specialized tables are not interchangeable. Temporal tables solve point-in-time history queries, ledger tables solve cryptographic verification, graph tables solve many-to-many relationships, and in-memory tables solve hot-path latency. Knowing when to use which is more important than memorizing syntax.
• JSON in T-SQL is heavily tested. Native JSON functions like JSON_OBJECT and JSON_ARRAYAGG (newer additions) often appear alongside the older OPENJSON and JSON_VALUE. Expect questions that ask you to construct JSON output for an external API.
• Regular expressions in SQL are recent additions to the T-SQL language family in SQL Server 2025 and Azure SQL. Many veterans missed their arrival entirely. Expect at least 2–4 questions on REGEXP_* functions.
• Fuzzy matching functions are the bridge between traditional SQL and AI-style data work. They’re often the “right answer” when a question describes finding similar but non-identical text — names, addresses, product descriptions.
• MCP servers are the newest topic in the entire exam. They let GitHub Copilot interact with your live database schema. Knowing the difference between a Copilot instruction file (static guidance) and an MCP server connection (live metadata) is a tested distinction.

Common Questions in This Domain

Expect scenarios like:

• A scenario describing a slow customer search that needs to handle typos. You’re shown four options and must choose between full-text search, fuzzy matching with EDIT_DISTANCE, a LIKE wildcard, and a vector search. The correct answer depends on details in the scenario — read carefully.
• A case study where a developer wants Copilot to use real-time schema metadata. The answer is “use an MCP server endpoint,” not “export the dacpac” or “paste the schema into the prompt.”
• Drag-and-drop ordering questions for graph queries with the MATCH operator.
• T-SQL snippets where you must identify the missing JSON function or window function clause.

Study Tips

• Build a sandbox database with all the table types. Create a temporal table, populate it with changes, then run an AS OF query. Create a graph table with NODE and EDGE syntax. Create a ledger table and verify its hash chain. Hands-on muscle memory beats reading.
• Practice writing JSON output and consuming JSON input with OPENJSON and JSON_VALUE. The syntax for the WITH clause on OPENJSON is a common stumbling block.
• Install the MCP server for SQL Server in VS Code and open a Copilot session against a real database. The 30 minutes you spend doing this once is worth more than two hours of reading documentation.
• Memorize the difference between EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE. They return different things — one is an integer, one is a 0–1 similarity, and one is the Jaro-Winkler score.

Domain 2: Secure, Optimize, and Deploy Database Solutions (35–40%)

In-Depth Breakdown

This domain is, in my experience, where most candidates underestimate the prep time required. It’s tied for the largest weight on the exam, and it covers four sub-areas that span security, performance, CI/CD, and Azure integration — four full disciplines compressed into one domain.

1. Implement data security and compliance. Designing and implementing data encryption (Always Encrypted, column-level encryption), Dynamic Data Masking, Row-Level Security (RLS), object-level permissions, passwordless secure database access, auditing, securing model endpoints with Managed Identity, and securing GraphQL, REST, and MCP endpoints.

2. Optimize database performance. Recommending database configurations, preserving data integrity through transaction isolation levels and concurrency controls, evaluating query performance using execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight, and resolving issues like blocking and deadlocks.

3. Implement CI/CD using SQL Database Projects. This is a topic many traditional DBAs have never touched: designing testing strategies (unit tests, integration tests), managing reference/static data in source control, building and validating database models with SDK-style SQL Database Projects, configuring source control, branch management, pull requests, conflict resolution, secrets management, schema drift detection, and implementing deployment pipeline controls including branching policies, triggers, approvals, authentication tables, and code owners.

4. Integrate SQL solutions with Azure services. Creating configuration files for Data API Builder (DAB), configuring entities for REST and GraphQL (data caching, pagination, searching, filtering), exposing database objects/stored procedures/views including GraphQL relationships, configuring DAB deployment, recommending Azure Monitor configurations (Application Insights, Log Analytics), and handling changes via Change Event Streaming (CES), Change Data Capture (CDC), Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps.

Key Concepts

The concepts that drive the most exam questions:

• The encryption hierarchy. Always Encrypted (with deterministic vs randomized encryption types) protects data from DBAs themselves — keys live with the client. Column-level encryption is server-side. TDE is at the file level. Pick the right one based on the threat model in the scenario.
• RLS predicates come in two types: filter predicates (silently exclude rows) and block predicates (raise errors on violations). Knowing the difference matters.
• Managed Identity for model endpoints is the recommended way to authenticate from SQL to external AI services without storing secrets. This is heavily favored as a “best practice” answer.
• SDK-style SQL Database Projects are newer than the first SSDT projects and use a simplified .sqlproj format. Questions frequently hinge on this distinction.
• DAB (Data API Builder) is Microsoft’s CLI-based tool for exposing SQL data as REST or GraphQL APIs through a JSON config. Know the structure of dab-config.json, how to declare entities, and how to map relationships.
• Change handling pattern selection. CDC tracks changes with full history. Change Tracking is lighter. CES (Change Event Streaming) is the newest and pushes events out. Azure Functions with SQL trigger binding wires those changes to serverless code.

Common Questions in This Domain

Expect:

• A multi-step case where a developer needs to expose a stored procedure as a REST endpoint with pagination. The answer involves DAB config edits, not writing custom controllers.
• Scenarios that pit Always Encrypted against TDE versus Dynamic Data Masking. The deciding factor is usually “who in the system needs to be blocked from seeing the data.”
• Performance troubleshooting questions where Query Store wait stats or a specific DMV (sys.dm_exec_query_stats, sys.dm_db_index_usage_stats) is the right starting point.
• CI/CD scenarios, including schema drift between development and production. The expected answer mentions schema comparison via SQL Database Projects.
• Questions about preventing branch policy bypass — code owners, required reviewers, and branch protection rules.

Study Tips

• Stand up a real CI/CD pipeline with a SQL Database Project in GitHub Actions. Push a schema change, observe the build/validate/deploy stages, and intentionally cause a schema drift to see how the tooling reports it.
• Join Microsoft Reactor and engage with developers live, join 27th May 2026 Event.
• Configure DAB locally. Install the DAB CLI, create a dab-config.json, expose a real SQL table as both REST and GraphQL, and call it. This single exercise teaches you 6–8 questions’ worth of material.
• Walk through Query Store like a tour guide. Open Query Store reports in SSMS — Top Resource Consuming Queries, Regressed Queries, and Query Wait Statistics. The exam treats Query Store as the default troubleshooting starting point.
• Practice transaction isolation level questions. The four standard levels plus snapshot isolation are tested through scenarios including dirty reads, non-repeatable reads, and phantom reads.

Resources

• Microsoft Learn path “Implement security in Azure SQL.”
• The Data API Builder GitHub repository and quickstart docs
• Microsoft Learn module “Get started with SQL Database Projects.”
• Azure SQL Database documentation for Always Encrypted with VBS enclaves
• The SQL Server “Query Store” documentation tree

Domain 3: Implement AI Capabilities in Database Solutions (25–30%)

In-depth Breakdown

This is the smallest domain by weight, but it’s the reason the certification exists. Microsoft splits AI capabilities into three sub-areas:

1. Design and implement models and embeddings. Evaluating external models (multimodal, multilingual, sizes, structured output), creating and managing external models, choosing an embedding maintenance method (table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, Microsoft Foundry), identifying which columns to include in embeddings, designing chunks for embeddings, and generating embeddings.

2. Design and implement an intelligent search. Choosing between full-text, semantic vector, and hybrid search; designing for the vector data type, vector indexes, and vector size; using vector functions (VECTOR_NORMALIZE, VECTOR_DISTANCE, VECTORPROPERTY, VECTOR_SEARCH); choosing between Approximate Nearest Neighbor (ANN) and Exact Nearest Neighbor (ENN); evaluating vector index types and metrics; implementing vector and hybrid search; implementing Reciprocal Rank Fusion (RRF); and evaluating performance of vector and hybrid search.

3. Design and implement Retrieval-Augmented Generation (RAG). Identifying RAG use cases, creating a prompt using the sp_invoke_external_rest_endpoint stored procedure, converting structured data to JSON for language model processing, sending results to a language model, and extracting language model responses.

Key Concepts

The high-leverage concepts:

• Embeddings represent semantic meaning. Choose columns containing rich natural-language content (descriptions, comments, articles) — not categorical fields, IDs, or numeric values. This is one of the most common single-question topics in the entire exam.
• The chunking strategy remains its own discipline. Documents that are too long lose precision; chunks that are too short lose context. Overlap between chunks helps preserve continuity.
• ANN vs ENN. Exact Nearest Neighbor checks every vector and is precise but slow. Approximate Nearest Neighbor uses index structures (often DiskANN-based in SQL platforms) and trades a small accuracy loss for major speed gains. ANN wins for production-scale workloads; ENN is acceptable for small datasets or correctness validation.
• Vector distance measures (cosine, Euclidean, dot product) measure similarity differently. Cosine distance is the default for normalized embeddings.
• Hybrid search with RRF combines results from full-text search and vector search using rank-based fusion. It’s typically the “best answer” when a question refers to both keyword exactness and semantic recall as requirements.
• sp_invoke_external_rest_endpoint is the in-database way to call language models. You don’t need an external orchestrator like LangChain — the database itself becomes the RAG pipeline.
• Embedding maintenance. When source data changes, embeddings must be regenerated. Change Tracking is light, CDC is detailed, triggers are immediate, Azure Functions with SQL trigger binding offloads work asynchronously, and Microsoft Foundry handles it through managed pipelines.

Common Questions in This Domain

Expect:

• A case study where you’re shown columns from a table (vehicleLocation, incidentDescription, incidentType, severityScore) and asked which to embed. The response is the rich text column (incidentDescription).
• Scenario questions about a search system that needs both exact keyword matching and semantic similarity. The answer is hybrid search with RRF.
• Drag-and-drop questions ordering the steps of a RAG pipeline: convert input to embedding, run VECTOR_SEARCH, format results as JSON, call the model via sp_invoke_external_rest_endpoint, and parse the response.
• Performance questions: an ENN-based search is too slow at scale. The fix is moving to an ANN vector index.
• Embedding maintenance questions: the source content gets edited frequently, and the system needs near-real-time freshness — pick the trigger-based or CES-based approach over batch CDC.

After reviewing the DP-800 exam domains, use exam prep websites with updated practice questions to test each domain under real exam-style pressure.

Study Tips

• Generate embeddings against a real table. Use Azure OpenAI’s text-embedding-3-small model and store the vectors in a VECTOR(1536) column. Run a similarity search. Watching it work is the fastest path to retention.
• Build a tiny end-to-end RAG. Embed a knowledge base, accept a user question, search the vectors, send the top results plus the question to a chat completion endpoint via sp_invoke_external_rest_endpoint, and return the grounded answer. This single exercise covers 60% of the AI domain.
• Read the documentation for RRF. It’s a small algorithm, but exam questions often test the formula’s behavior, not just its name.
• Learn the difference between similarity (higher = more similar) and distance (lower = more similar) for vector functions. VECTOR_DISTANCE returns distance; cosine similarity = 1 − cosine distance. This trips up people regularly.

Resources

• Azure SQL Database “Intelligent applications” documentation
• The “Vector data type in Azure SQL” documentation page
• Microsoft Learn module “Generate embeddings in Azure SQL Database.”
• The sp_invoke_external_rest_endpoint reference page
• Azure OpenAI Service documentation for embedding models

Time Allocation Recommendation

For most candidates with working SQL development experience, I recommend roughly the following split over a 6-week prep timeline:

Week	Focus	Hours
1	Domain 1: Object design, advanced T-SQL fundamentals	12–15
2	Domain 1: JSON, regex, fuzzy matching, AI-assisted tools (Copilot, MCP)	12–15
3	Domain 2: Security, encryption, RLS, query optimization	12–15
4	Domain 2: SQL Database Projects, CI/CD, DAB, change handling	12–15
5	Domain 3: Embeddings, vector search, RAG	14–18
6	Full-length practice exams, weak-area review, exam strategy	10–12

Total: about 75–90 hours, weighted slightly more toward Domain 3 because it contains the most material that’s genuinely new to most candidates. Don’t be misled by Domain 3’s lower weight on the exam — the volume of new concepts per percentage point is the highest of any domain.

If you’re already comfortable with vector databases, embeddings, and RAG from work elsewhere, redirect time from Week 5 into Week 4 (CI/CD and DAB) — those topics catch experienced AI engineers off-guard.

Understand which Microsoft certification fits your role in the [DP-800 vs DP-300: Which Microsoft Certification Should You Take in 2026?]

Difficulty Level Per Domain

Based on candidate feedback patterns and the structure of the exam:

Domain 1 — Moderate. Most experienced T-SQL developers find the bulk of this domain familiar. The genuine hard parts are the AI-assisted tooling section (MCP servers, Copilot instruction files) and regex/fuzzy matching, which are recent additions to T-SQL.

Domain 2 — Hard. The breadth here is enormous. Security alone could be its own exam, and adding CI/CD with SDK-style projects, DAB configuration, and change-handling patterns means you’re being tested across four distinct disciplines. CI/CD is the single most common weak area I see.

Domain 3 — Hard for SQL specialists, easier for AI engineers. If you’ve never worked with embeddings or vector search before, this is where you’ll feel the steepest learning curve. The mathematical concepts (cosine similarity, RRF formula) are simple, but the operational decisions (chunking, index type, maintenance method) take real practice to internalize.

Overall, the exam difficulty is Intermediate but skewed toward the upper end because of how new the AI and CI/CD topics are.

Click here: DP-800 Salary Impact: How Much Can a SQL Database Expert Earn in 2026?

Interconnections Between Domains

The domains aren’t isolated. The exam frequently asks questions that span multiple domains, and noticing these connections helps you answer faster and more accurately.

Domain 1 ↔ Domain 3. The vector data type, JSON columns, and table design from Domain 1 are the storage substrate that Domain 3 builds on. You can’t store embeddings without designing the right table. Chunking strategy (Domain 3) interacts with table partitioning and index design (Domain 1).

Domain 2 ↔ Domain 3. Securing model endpoints with Managed Identity (Domain 2) is what makes RAG calls (Domain 3) safe in production. RLS (Domain 2) on a table with embeddings means your vector search results are automatically filtered to authorized rows — a powerful and tested pattern.

Domain 1 ↔ Domain 2. SQL Database Projects (Domain 2): source-control everything you build in Domain 1. Schema drift detection only works if the objects in Domain 1 are properly defined in the source.

All three domains. A question describing “build a secure semantic search over customer feedback with full CI/CD” simultaneously tests Domain 1 (table and JSON design), Domain 2 (security, deployment), and Domain 3 (embeddings, vector search). These multi-domain case studies are common.

Common Mistakes People Make

After watching dozens of candidates prepare for and take this exam, here are the recurring traps:

1. Treating Domain 3 as “the AI exam” and Domains 1 and 2 as warm-ups. Domain 3 is only 25–30% of your score. Stronger contestants often gain points in Domains 1 and 2, where the questions reward solid SQL fundamentals.

2. Skipping the AI-assisted tools section in Domain 1. Many DBAs have never touched GitHub Copilot or MCP servers. These topics generate 3–6 exam questions and are easy points if you spend two hours setting them up locally.

3. Underestimating Data API Builder. DAB is a small tool, but Microsoft tests it deeply. Questions about config file structure, entity definition, and deployment are direct and unforgiving.

4. Confusing similarity with distance. Vector search returns distance values (lower = closer). Many candidates reflexively pick the highest number as “most similar” and get the question wrong.

5. Choosing the wrong column for embeddings. Embeddings work on semantic content, not categorical or numeric fields. When in doubt, pick the column with rich free-form text.

6. Memorizing syntax without understanding context. This exam loves scenario questions. Knowing that JARO_WINKLER_DISTANCE exists matters less than knowing when to use it instead of EDIT_DISTANCE or full-text search.

7. Skipping hands-on labs. Reading documentation gets you maybe 60% of the way there. The other 40% — the part that catches edge-case questions — only comes from breaking things in your own sandbox.

8. Cramming the day before. This exam tests integrated thinking across several technologies. Cramming hurts more than it helps. Sleep matters here.

Follow proven preparation methods in our [How to Pass DP-800 Exam: 7 Expert Strategies That Actually Work]

Final Word

DP-800 is a well-designed exam that genuinely reflects where SQL development is headed in 2026 and beyond. It rewards builders — people who write code, configure pipelines, and integrate systems — over pure memorizers.

If you treat the three domains with the respect their weightings suggest, build hands-on labs for each, and practice integrated case-study scenarios, you’ll walk into the test confident. The fact that this credential is brand-new also means certified candidates currently sit in a tiny, valuable slice of the talent market — which makes the prep investment one of the best returns available among Microsoft certifications right now.

Good luck. Build things, break things, and get certified.

Sources: Official DP-800 study guide on Microsoft Learn (March 12, 2026), Microsoft Skills Hub Blog announcements, official SQL Server 2025 and Azure SQL Database documentation, and Microsoft Fabric SQL database documentation.