DP-800 sample exam questions

15 exam-style questions for Microsoft DP-800: SQL AI Developer, with answers and explanations. Take them as a timed practice exam first if you want a score out of 1000.

Take the free practice exam DP-800 exam guide

Question 1Single choiceTopic 1

You have an Azure SQL database that contains the following SQL graph tables:
A NODE table named dbo.Person
An EDGE table named dbo.Knows
Each row in dbo.Person contains the following columns:
PersonID (int)
DisplayName (nvarchar(100))
You need to use a MATCH operator and exactly two directed Knows relationships to return the PersonID and DisplayName of people that are reachable from the person identified by an input parameter named @StartPersonId.
Which Transact-SQL query should you use?
1. A
2. B
3. C
4. D
Show answer

Correct answer: D

Why
Two directed hops need two edge aliases in one MATCH pattern: MATCH(p1-(k1)->p2-(k2)->p3), starting from p1 filtered to @StartPersonId and returning p3. Other options reverse direction, use one hop, or return the wrong node alias.
Question 2Single choiceTopic 1

You have a SQL database in Microsoft Fabric that contains a nvarchar (max) column named MessageText. An ID is always contained within the first paragraph of MessageText.
You need to write a Transact-SQL query that uses REGEXP_SUBSTR to extract the ID from MessageText.
What should you include in the query?
1. A
  Apply STRING_ESCAPE(MessageText, ‘json’) before calling REGEXP_SUBSTR.
2. B
  Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
3. C
  Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR.
4. D
  Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR.
Show answer

Correct answer: B

Why
REGEXP_SUBSTR does not accept nvarchar(max) as its input, and the ID sits in the first paragraph, so casting to nvarchar(4000) is enough. STRING_ESCAPE and COLLATE do not change the input type, and varchar(max) is still a max type.
Question 3Single choiceTopic 1

You have an Azure SQL database named ToDo that contains a table named dbo.ToDo.

Your company plans to develop an Azure Functions app to run whenever the rows in dbo.ToDo change. The app will process INSERT, UPDATE, and DELETE events by using the Azure SQL trigger binding.

You need to configure ToDo to support the planned app.

What should you do?
1. A
  Enable change tracking on ToDo and dbo.ToDo.
2. B
  On dbo.ToDo, create a Data Manipulation Language (DML) trigger that calls the Azure Functions HTTP endpoint.
3. C
  Enable change data capture (CDC) on ToDo and dbo.ToDo.
4. D
  On dbo.ToDo, create a Data Definition Language (DDL) trigger that calls the Azure Functions HTTP endpoint.
Show answer

Correct answer: A

Why
The Azure SQL trigger binding uses change tracking, which must be enabled on the database and on the table. CDC is not what the binding reads. DML or DDL triggers calling an HTTP endpoint are not how the binding detects row changes.
Question 4Drag and dropTopic 1

DRAG DROP

You have an Azure SQL database that stores sales data and contains tables named Sales and Products. Sales contains three columns named SalesDate, ProductKey, and TotalSale,

Sales is 10 TB and is loaded nightly by using a batch process. Most reporting queries scan large portions of Sales, filter on SalesDate or ProductKey, and use SUM() to aggregate TotalSale.

Products is relatively small and is used primarily for point lookups and joins to Sales.

You need to recommend which indexes to create to optimize the reporting queries. The solution must minimize storage requirements.

Which type of index should you recommend for each table? To answer, drag the appropriate index types to the correct tables. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

Move each item to the matching row:
- A clustered columnstore index
- A clustered rowstore index
- A nonclustered columnstore index
- A nonclustered rowstore index
- Sales:
- Products:
Show answer
- Sales: A clustered columnstore index
- Products: A clustered rowstore index
Why
Sales is a very large, batch-loaded table with scan and aggregate queries, so a clustered columnstore index gives the best compression and performance with no separate rowstore copy. Products is small and used for point lookups and joins, which a clustered rowstore index serves efficiently without extra storage.
Question 5HotspotTopic 1

HOTSPOT

You have an Azure SQL database that contains a table named dbo.SupportTickets. dbo.SupportTickets contains a JSON column named Payload and a datetime column CreatedAt.

You need to generate a report for the last seven days that meets the following requirements:

• Returns exactly one row per customer per day
• For each customer and day, returns the earliest ticket
• Includes the customer ID stored in Payload

How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Customer ID expression DENSE_RANK / JSON_VALUE / OPENJSON / ROW_NUMBER
Ranking function DENSE_RANK / JSON_VALUE / OPENJSON / ROW_NUMBER
WHERE rn = 1 / 7 / DATEDIFF(day, -7, SYSUTCDATETIME())
Show answer
- Customer ID expression JSON_VALUE
- Ranking function ROW_NUMBER
- WHERE rn = 1
Why
JSON_VALUE returns the scalar customer ID from Payload. ROW_NUMBER gives unique sequential numbers in each customer and day partition, so ties cannot produce duplicates as DENSE_RANK could. With CreatedAt ascending, rn = 1 is the earliest ticket, giving exactly one row per customer per day.
Question 6Single choiceTopic 1

You are developing an Azure SQL database solution from a locally cloned GitHub repository by using Microsoft Visual Studio Code and GitHub Copilot Chat.

You need to ensure that GitHub Copilot Chat can call the hosted GitHub MCP Server tools by using OAuth. The MCP server configuration must be scoped to the repository.

What should you do in Visual Studio Code?
1. A
  Create a personal access token (PAT) that has the repo scope and store the PAT in the .vscode\mcp.json file as the Authorization header.
2. B
  From the Command Palette, enter MCP: add server, select HTTP (HTTP or Server-Sent Events), enter https://api.githubcopilot.com/mcp/, and then save the configuration to the workspace settings.
3. C
  From the Command Palette, enter MCP: add server, select HTTP (HTTP or Server-Sent Events), enter https://api.githubcopilot.com/mcp/, and then save the configuration to the user settings.
4. D
  Create a personal access token (PAT) that has the repo scope and store the PAT in the .github\mcp.json file as the Authorization header.
Show answer

Correct answer: B

Why
Adding the remote HTTP server https://api.githubcopilot.com/mcp/ via MCP: add server and saving it to workspace settings writes .vscode/mcp.json, which scopes it to the repository, and VS Code then handles OAuth. Saving to user settings is not repo-scoped, and PAT headers avoid OAuth.
Question 7Single choiceTopic 2

You have an Azure SQL database named SalesDB that contains a table named dbo.Articles. dbo.Articles contains two million articles with embeddings. The articles are updated frequently throughout the day.
You query the embeddings by using VECTOR_SEARCH.
Users report that semantic search results do NOT reflect the updates until the following day.
You need to ensure that the embeddings are updated whenever the articles change. The solution must minimize CPU usage on SalesDB.
Which embedding maintenance method should you implement?
1. A
  Modify the query to use VECTOR_DISTANCE instead of VECTOR_SEARCH.
2. B
  Enable change data capture (CDC) on dbo.Articles and use an Azure Functions app to process CDC changes.
3. C
  Run an hourly Transact-SQL job that regenerates embeddings for all the rows in dbo.Articles.
4. D
  On dbo.Articles, create a trigger that calls AI_GENERATE_EMBEDDINGS for each inserted or updated row.
Show answer

Correct answer: B

Why
Change data capture feeds changes to an Azure Functions app, which regenerates only changed embeddings outside the database, minimizing CPU on SalesDB. A trigger calling AI_GENERATE_EMBEDDINGS adds load to every write, hourly full regeneration is wasteful, and VECTOR_DISTANCE does not refresh embeddings.
Question 8Multiple responseTopic 2

You have a Microsoft SQL Server 2025 instance that contains a database named SalesDB. SalesDB supports a Retrieval Augmented Generation (RAG) pattern for internal support tickets. The SQL Server instance runs without any outbound network connectivity.
You plan to generate embeddings inside the SQL Server instance and store them in a table for vector similarity queries.
You need to ensure that only a database user account named AIApplicationUser can run embedding generation by using the model.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
1. A
  Grant the CONTROL permission on SalesDB to AIApplicationUser.
2. B
  Create a database audit specification on SalesDB owned by AIApplicationUser.
3. C
  Grant the EXECUTE permission on the external model project to AIApplicationUser.
4. D
  Create an external model project by using ONNX runtime and local paths.
5. E
  Create an external model project that points to a Microsoft Foundry REST endpoint.
Show answer

Correct answer: C, D

Why
With no outbound connectivity, the model must run locally, so create an external model using ONNX runtime with local paths, not a Foundry REST endpoint. Granting EXECUTE on that model to AIApplicationUser limits who can generate embeddings. CONTROL on the database is far too broad, and an audit spec does not restrict access.

Customer ID expression	DENSE_RANK / JSON_VALUE / OPENJSON / ROW_NUMBER
Ranking function	DENSE_RANK / JSON_VALUE / OPENJSON / ROW_NUMBER
WHERE rn =	1 / 7 / DATEDIFF(day, -7, SYSUTCDATETIME())

Question 9HotspotTopic 2

HOTSPOT
You have a SQL database in Microsoft Fabric that contains the following functions:
A multi-statement table-valued function (TVF) named Sales.mstvf_OrderStatus() that returns order status information
A scalar user-defined function (UDF) named dbo.ufn_GetTaxMultiplier (@TaxAmt money, @StateCode char(2)) that returns a numeric multiplier used in tax calculations
Reporting queries frequently join Sales.mstvf_OrderStatus() to a table named Sales.SalesOrderHeader and return large result sets. A performance review shows that the queries produce inconsistent execution plans.

During a code review, a developer discovers that the following Transact-SQL statement produced an error.

EXEC @ret = ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = ‘WA’;
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

You can use GETDATE() in dbo.ufn_GetTaxMultiplier to produce nondeterministic results.	Yes / No
Rewriting Sales.mstvf_OrderStatus() as an inline table TVF will reduce the number of inconsistent execution plans.	Yes / No
Replacing ufn_GetTaxMultiplier with dbo.ufn_GetTaxMultiplier in the EXEC function statement will resolve the error.	Yes / No

Show answer

You can use GETDATE() in dbo.ufn_GetTaxMultiplier to produce nondeterministic results. Yes
Rewriting Sales.mstvf_OrderStatus() as an inline table TVF will reduce the number of inconsistent execution plans. Yes
Replacing ufn_GetTaxMultiplier with dbo.ufn_GetTaxMultiplier in the EXEC function statement will resolve the error. Yes

Why

T-SQL UDFs may call nondeterministic built-ins such as GETDATE. Inline TVFs are expanded into the calling query, so the optimizer uses real statistics rather than the fixed estimates of multi-statement TVFs. Scalar UDFs can be run with EXEC, and adding the schema name resolves the object reference.

Question 10Single choiceTopic 2

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an SDK-style SQL database project stored in a Git repository. The project targets an Azure SQL database.
The CI build fails with unresolved reference errors when the project references system objects.
You need to update the SQL database project to ensure that dotnet build validates successfully by including the correct system objects in the database model for Azure SQL Database.
Solution: Add an artifact reference to the Azure SQL Database master.dacpac file.
Does this meet the goal?
1. A
  Yes
2. B
  No
Show answer

Correct answer: A

Why
SDK-style SQL projects need the correct system object definitions for the target platform. For Azure SQL Database, referencing the Azure master.dacpac as an artifact reference supplies those definitions so dotnet build resolves system objects. The on-premises SQL Server master.dacpac would be the wrong model.
Question 11Single choiceTopic 2

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.

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?
1. A
  Add a post-deployment script that inserts reference rows by using IF NOT EXISTS or MERGE logic.
2. B
  Restore a backup after each deployment.
3. C
  Store the reference values in GitHub repository secrets.
Show answer

Correct answer: A

Why
A post-deployment script in the SQL project runs on every deploy, so making it idempotent with IF NOT EXISTS or MERGE means reruns produce the same rows without duplicates. Restoring a backup would overwrite production data, and GitHub secrets are not for reference data.

Question 12HotspotTopic 2

HOTSPOT

You have a SQL database in Microsoft Fabric that uses the default settings for a newly created database and contains a table named Sales.Orders.

You have an application that uses two stored procedures to access Sales.Orders.

While monitoring database activity, you discover the following:

• sys.dm_exec_requests shows multiple sessions in a suspended state with wait_type = LCK_M_X. All the sessions show the same wait_resource, which maps to Sales.Orders, and the same nonzero blocking_session_id.
• sys.dm_exec_input_buffer(blocking_session_id, NULL) returns a last submitted command of BEGIN TRANSACTION UPDATE Sales.Orders.
• sys.dm_exec_sessions for the blocking session shows status = sleeping and open_transaction_count = 1.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

The blocking is caused by an uncommitted explicit transaction in the blocking session that is holding locks.	Yes / No
While an UPDATE operation on Sales.Orders is occurring, SELECT statements will be blocked.	Yes / No
Joining sys.dm_tran_locks to sys.dm_exec_requests will show which session holds locks involved in the blocking chain.	Yes / No

Show answer

The blocking is caused by an uncommitted explicit transaction in the blocking session that is holding locks. Yes
While an UPDATE operation on Sales.Orders is occurring, SELECT statements will be blocked. No
Joining sys.dm_tran_locks to sys.dm_exec_requests will show which session holds locks involved in the blocking chain. Yes

Why

A sleeping session with open_transaction_count = 1 has an open transaction that still holds its locks. Fabric SQL databases enable read committed snapshot by default, so readers use row versions and are not blocked. sys.dm_tran_locks lists lock holders and waiters by session, and requests add each waiter's blocking_session_id.

Question 13Single choiceTopic 2

You have an Azure SQL database that contains a table named Customer. Customer contains the following columns:

• NationalIDNumber is unique per customer.
• InvestigationNotes contains free-form text.

You have a stored procedure that performs point lookups by NationalIDNumber and returns InvestigationNotes in the query results without filtering on InvestigationNotes.

You need to encrypt both columns by using Always Encrypted and the highest security possible.

Which type of Always Encrypted encryption should you use for each column?
1. A
  deterministic for NationalIDNumber and randomized for InvestigationNotes
2. B
  deterministic for NationalIDNumber and deterministic for InvestigationNotes
3. C
  randomized for NationalIDNumber and randomized for InvestigationNotes
4. D
  randomized for NationalIDNumber and deterministic for InvestigationNotes
Show answer

Correct answer: A

Why
Point lookups on NationalIDNumber need equality comparison, which only deterministic encryption supports. InvestigationNotes is never filtered, so randomized encryption gives the strongest protection. Randomized on the lookup column would break the lookups, and deterministic on notes weakens security needlessly.
Question 14Single choiceTopic 3

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_GENERATE_EMBEDDINGS 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?
1. A
  Regenerate embeddings for the existing rows.
2. B
  Normalize the vector lengths before storing new embeddings.
3. C
  Convert the Embedding column to nvarchar(max).
4. D
  Create a vector index on dbo.Products.Embedding.
Show answer

Correct answer: A

Why
Embeddings from different models are not compatible, and text-embedding-3-small defaults to a different dimension than the VECTOR(1536) from ada-002 and a different vector space. Existing rows must first be regenerated with the new model so all vectors are comparable. Normalizing, nvarchar storage, or an index will not fix this.

Question 15HotspotTopic 3

HOTSPOT
You have an Azure SQL database that contains a table named stores. stores contains a column named description and a vector column named embedding.

You need to implement a hybrid search query that meets the following requirements:
Uses full-text search on description for the keyword portion
Returns the top 20 results based on a combined score that uses a weighted formula of 60% vector distance and 40% full-text rank
How should you configure the query components? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Semantic query operator/function:	VECTOR_DISTANCE and order by distance ascending / VECTOR_SEARCH with METRIC cosine and TOP_N / VECTORPROPERTY to calculate the similarity between two vectors
Keyword retrieval operator/function:	CONTAINSTABLE on description and return ranked matches / FREETEXTTABLE on description for keyword scoring / JSON_VALUE extraction from description for keyword scoring
Final ranking expression:	order by (distance * 0.6) + ((1.0 - RANK/1000.0) * 0.4) / order by (distance * 0.6) + (RANK * 0.4) / order by (distance + RANK), and then apply TOP 20

Show answer

Semantic query operator/function: VECTOR_DISTANCE and order by distance ascending
Keyword retrieval operator/function: CONTAINSTABLE on description and return ranked matches
Final ranking expression: order by (distance * 0.6) + ((1.0 - RANK/1000.0) * 0.4)

Why

VECTOR_DISTANCE computes exact distance without needing a vector index, where lower is closer. CONTAINSTABLE returns ranked keyword matches with a RANK column. Higher RANK is better, so it is normalized and inverted to match distance, letting the 60/40 weighted sum sort ascending. VECTOR_SEARCH would also be plausible with an index.