Microsoft DP-800 Practice Exam Questions & Answers
Microsoft DP-800 Exam Details
Key details for this exam, checked against the published exam outline
- Exam Code
- DP-800
- Full Name
- Developing AI-Enabled Database Solutions
- Issuing Body
- Microsoft
- Question Format (Our Bank)
- Multiple Choice, Hotspot, Drag & Drop, Case Studies
- Delivery
- Online proctored or at a Pearson VUE test centre
- Full Question Set
- 111 questions Get All Questions
Free DP-800 Practice Questions
Each question shows the correct answer and an explanation of why it is right
Question 1 Domain 3 Implement AI capabilities in database solutions
You have an Azure SQL database named AdventureWorksDB that contains a table named dbo.Employee.
You have a C# Azure Functions app that uses an HTTP-triggered function with an Azure SQL input binding to query dbo.Employee.
You are adding a second function that will react to row changes in dbo.Employee and write structured logs.
You need to configure AdventureWorksDB and the app to meet the following requirements:
* Changes to dbo.Employee must trigger the new function within five seconds.
* Each invocation must processes no more than 100 changes.
Which two database configurations should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Azure Functions' Azure SQL trigger requires change tracking to be enabled on the source table. Microsoft's SQL trigger documentation states that setting up change tracking for the Azure SQL trigger requires two steps: enable change tracking on the database and enable change tracking on the table being monitored. Since the question asks specifically which database configurations you should perform, enabling change tracking on dbo.Employee is one of the required database-side steps.
To meet the latency requirement that changes trigger the function within five seconds, the relevant trigger setting is Sql_Trigger_PollingIntervalMs. Microsoft documents this setting as the delay, in milliseconds, between processing each batch of changes, and a value of 5000 means the trigger polls every 5 seconds.
A few clarifications about the other options:
B is not the documented setting name. The documented app setting is Sql_Trigger_BatchSize or host setting MaxBatchSize, not ''SetSql Trigger MaxBatchSize''. The screenshot wording suggests a distractor.
D is also required in practice for the trigger to work, but the question asks for two answers and includes the polling setting plus the table-level CT setting as the actionable choices presented.
F is wrong because the Azure SQL trigger uses change tracking, not CDC.
Question 2 Domain 3 Implement AI capabilities in database solutions
You have a database that contains production data. The schema is stored in a Git repository as an SDK-style SQL database project and contains the following reference data.
Name
Data type
RefID
int, identity
Code
nvarchar(10)
CreatedDate
datetime2
Description
nvarchar(255)
A deployment pipeline can be rerun automatically when a transient failure occurs.
You need to deploy the reference data as part of the same CI/CD process. Rerunning the pipeline must produce the same outcome and must NOT create duplicate rows.
What should you do?
When deploying reference data through a CI/CD pipeline that can be rerun automatically, the deployment must be idempotent—producing the same outcome each time without creating duplicate rows. Instead of simple INSERT statements, you should use a MERGE statement or a DELETE followed by INSERT pattern with appropriate WHERE conditions that prevent duplicates. MERGE is particularly effective as it handles INSERT, UPDATE, and DELETE operations in a single statement based on whether records already exist, making it naturally idempotent for reference data deployments.
Question 3 Domain 2 Secure, optimize, and deploy database solutions
You have a database named DB1. The schema is stored in a Git repository as an SDK-style SQL database project.
You have a GitHub Actions workflow that already runs dotnet build and produces a database artifact.
You need to add a deployment step that publishes the dacpac file to an Azure SQL database by using the secrets stored in GitHub repository secrets
What should you include in the workflow?
A)

B)

C)

D)

The correct workflow step is Option C because it uses the Azure SQL GitHub Action to publish a .dacpac file and reads the connection string from GitHub repository secrets, which is exactly what the requirement asks for. Microsoft's Azure SQL GitHub Actions guidance shows using azure/sql-action@v2 with a connection string stored in secrets and a DACPAC path for deployment.
The key parts that make C correct are:
uses: azure/sql-action@v2
action: publish
path: bin/Debug/db1.dacpac
connection-string: ${{ secrets.SQL_CONNECTION_STRING }}
That matches the documented publish pattern for deploying a DACPAC to Azure SQL Database from GitHub Actions. Microsoft and the Azure SQL action documentation both describe Publish as the deployment action for applying a DACPAC to a target database, while Extract is used to create a DACPAC from an existing database, not deploy one.
Why the other options are incorrect:
A uses an environment variable defined inline with a visible connection string rather than using GitHub repository secrets, which does not meet the requirement.
B uses action: extract, which would create a DACPAC from a database instead of publishing the existing DACPAC artifact.
D passes a target connection string to dotnet build, but the question says the workflow already runs dotnet build and produces a database artifact. The missing step is the deployment/publish step, not another build step. Microsoft's SQL project automation guidance separates build the DACPAC from publish the DACPAC.
Get the complete DP-800 question set
$20$40
Question 4 Domain 1 Design and develop database solutions
You have an Azure SQL database That contains a table named dbo.Products, dbo.Products contains three columns named Embedding Category, and Price. The Embedding column is defined as VECTOR(1536).
You use Ai_GENERME_EMBEDOINGS and VECTOR_SEARCH to support semantic search and apply additional filters on two columns named Category and Price.
You plan to change the embedding model from text-embedding-ada-002 to text-embedding-3-smalL Existing rows already contain embeddings in the Embedding column.
You need to implement the model change. Applications must be able to use VECTOR_SEARCH without runtime errors.
What should you do first?
When you change embedding models, the stored vectors should be treated as belonging to a different embedding space unless you intentionally keep the entire corpus consistent. Microsoft's vector guidance notes that when most or all embeddings are replaced with fresh embeddings from a new model, the recommended practice is to reload the new embeddings and, for large-scale replacement scenarios, consider dropping and recreating the vector index afterward so search quality remains predictable.
This question also says applications must continue to use VECTOR_SEARCH without runtime errors. VECTOR_SEARCH requires compatible vector dimensions, and the vector column already exists. Azure OpenAI documentation shows that text-embedding-ada-002 is fixed at 1536 dimensions and text-embedding-3-small supports up to 1536 dimensions. That means the migration can remain compatible with a VECTOR(1536) column, but the right implementation step is still to re-embed the existing rows so the table does not contain a mixed corpus produced by different models.
The other options are not appropriate:
B normalization does not solve a model migration problem.
C converting the vector column to nvarchar(max) would break vector-native search design.
D a vector index improves performance, but it does not migrate old embeddings to the new model.
Question 5 Domain 3 Implement AI capabilities in database solutions
You have an Azure SQL database that supports the OLTP workload of an order-processing application.
During a 10-minute incident window, you run a dynamic management view query and discover the following:
Session 72 is sleeping with open_transaction_count = 1.
Multiple other sessions show blocking_session_id = 72 in sys.dm_exec_requests.
sys.dm_exec_input_buffer(72, NULL) returns only BEGIN TRANSACTION UPDATE Sales.Orders.
Users report that updates to Sales.Orders intermittently time out during the incident window. The timeouts stop only after you manually terminate session 72.
What is a possible cause of the blocking?
The best explanation is an open explicit transaction. During the incident, session 72 was sleeping but still had open_transaction_count = 1, and sys.dm_exec_input_buffer(72, NULL) showed only BEGIN TRANSACTION UPDATE Sales.Orders. That pattern indicates the session executed an update inside an explicit transaction and then remained idle without committing or rolling back, while still holding locks. Other sessions showing blocking_session_id = 72 is the expected symptom of that situation. Microsoft explains that blocking occurs when one session holds a lock on a resource and another session requests a conflicting lock, and sleeping sessions can continue to block if they retain locks through an open transaction.
This also fits the observed behavior that the timeouts stopped only after session 72 was terminated. Killing the session would roll back the active transaction and release the locks, allowing waiting updates to continue. That is much more consistent with an uncommitted transaction than with a deadlock, because deadlocks are normally detected and one session is chosen as the victim automatically rather than persisting until manual termination.
Question 6 Domain 3 Implement AI capabilities in database solutions
You need to recommend a solution for the development team to retrieve the live metadata. The solution must meet the development requirements.
What should you include in the recommendation?
The best recommendation is to use an MCP server. In the official DP-800 study guide, Microsoft explicitly lists skills such as configuring Model Context Protocol (MCP) tool options in a GitHub Copilot session and connecting to MCP server endpoints, including Microsoft SQL Server and Fabric Lakehouse. That makes MCP the exam-aligned mechanism for enabling AI-assisted tools to work with live database context rather than static snapshots.
This also matches the stated development requirement: the team will use Visual Studio Code and GitHub Copilot and needs to retrieve live metadata from the databases. Microsoft's documentation for GitHub Copilot with the MSSQL extension explains that Copilot works with an active database connection, provides schema-aware suggestions, supports chatting with a connected database, and adapts responses based on the current database context. Microsoft also documents MCP as the standard way for AI tools to connect to external systems and data sources through discoverable tools and endpoints.
The other options do not satisfy the ''live metadata'' requirement as well:
A .dacpac is a point-in-time schema artifact, not live metadata.
A Copilot instruction file provides guidance, not live database discovery.
Including the database project in the repository helps source control and deployment, but it still does not provide live database metadata by itself.
Question 7 Domain 2 Secure, optimize, and deploy database solutions
You need to enable similarity search to provide the analysts with the ability to retrieve the most relevant health summary reports. The solution must minimize latency.
What should you include in the solution?
The correct answer is D because the requirement is to enable similarity search over embedding vectors and to minimize latency. Microsoft documents that CREATE VECTOR INDEX is specifically used to create an index on vector data for approximate nearest neighbor (ANN) search, which is designed to accelerate vector similarity queries compared to exact k-nearest-neighbor scans.
This matches the scenario exactly. The VehicleHealthSummary table already includes an Embeddings (vector(1536)) column. In Microsoft SQL platforms, embeddings are stored in vector columns and queried for semantic similarity. To improve performance and reduce response time, Microsoft recommends a vector index, not a regular B-tree nonclustered index and not a full-text index. A vector index is purpose-built for finding the most similar vectors efficiently.
The other options are not appropriate:
A would require manual comparison logic and would increase latency rather than minimize it.
B is incorrect because a standard nonclustered index is not the index type used for vector similarity operations.
C is incorrect because full-text indexes are for textual token-based search, not numeric vector embeddings.
Microsoft's current documentation is explicit that vector indexes support approximate nearest neighbor search, and that the optimizer can use the ANN index automatically for vector queries. That is the exam-aligned design choice when the goal is fast retrieval of the most relevant health summary reports from an embeddings column.
What the Microsoft DP-800 Exam Covers
Exam domains verified against the Official Microsoft DP-800 exam guide, last checked November 2026.
Domain 1 Design and develop database solutions
35% - 40%Create tables with appropriate data types, indexes, and constraints. Build programmability objects including views, functions, stored procedures, and triggers. Write advanced T-SQL code featuring CTEs, window functions, JSON handling, regular expressions, graph queries, and correlated queries. Use AI-assisted tools like GitHub Copilot to write and optimize T-SQL code safely.
Sample question from this domain above Q4
Domain 2 Secure, optimize, and deploy database solutions
35% - 40%Implement encryption, Dynamic Data Masking, Row-Level Security, and object-level permissions. Evaluate query performance using execution plans and DMVs. Set up CI/CD pipelines with SQL Database Projects, manage version control and deployment approvals, and integrate SQL solutions with Azure services including Data API builder and change data capture methods.
Domain 3 Implement AI capabilities in database solutions
25% - 30%Create and manage external AI models for embeddings. Design and implement vector search using native SQL types and indexes. Build retrieval-augmented generation workflows by converting structured data to JSON, invoking external REST endpoints, and extracting language model responses. Evaluate vector search performance and choose between full-text, semantic, and hybrid search approaches.
DP-800 Exam FAQ
Common questions about the exam itself
What skills do I need before taking DP-800?
Is DP-800 harder than other Microsoft database exams?
How long should I study for DP-800?
Which objective area do candidates find hardest on DP-800?
What happens on exam day for DP-800?
What is the passing score for DP-800 and how is it calculated?
Can I retake DP-800 if I fail and what are the rules?
How long is the DP-800 certification valid and what renewal requires?
Which job role does DP-800 prepare me for?
How does DP-800 relate to other Microsoft data certifications?
Other Microsoft Exams
Free practice questions for more Microsoft certification exams
