DP-800 Sample Questions Answers

where exists 2. where p.PatientID = pr.PatientID Otherwise we filter PatientID twice. Having in the subquery does not work without group by and the date filter does not work without where.

Verified Answer : =

Vector index column → embedding

Vector search function → VECTOR_SEARCH

Final clause → ORDER BY s.distance

The first selection is embedding because CREATE VECTOR INDEX must target the column that stores vector data. Microsoft documents the syntax as CREATE VECTOR INDEX ... ON object (vector_column) , so the index belongs on dbo.Products.embedding , not on distance or product_name .

The second selection is VECTOR_SEARCH . This function is designed for vector similarity retrieval and works with vector indexes in SQL database in Microsoft Fabric . In the shown code, the supplied @query_vector VECTOR(1536) is compared against the embedding column using cosine distance, and TOP_N = 10 requests the ten nearest matches. Microsoft specifically documents VECTOR_SEARCH for approximate nearest-neighbor searches and its integration with vector indexes.

The final clause is ORDER BY s.distance because VECTOR_SEARCH returns a distance value representing how far each stored vector is from the supplied query vector. With cosine distance, smaller distance means greater similarity , so ascending order returns the most relevant products first.

Therefore, the completed selections are:

CREATE VECTOR INDEX idx_products_embedding

ON dbo.Products (embedding)

WITH (METRIC = ' cosine ' , TYPE = ' DiskANN ' );

FROM VECTOR_SEARCH (

TABLE = dbo.Products AS t,

COLUMN = embedding,

SIMILAR_TO = @query_vector,

METRIC = ' cosine ' ,

TOP_N = 10

) AS s

ORDER BY s.distance;

So the verified hotspot answers are embedding → VECTOR_SEARCH → ORDER BY s.distance .

For an Azure App Service using a user-assigned managed identity to connect to Azure SQL Database with Microsoft Entra authentication , the required database-side step is to create a database user from the external provider , then grant the needed database roles. Microsoft’s Azure SQL documentation for managed identities states that to let a managed identity access the target database, you create a SQL user for that identity by using:

CREATE USER [ < identity-name > ] FROM EXTERNAL PROVIDER;

and then assign the appropriate roles.

That makes db_datareader and db_datawriter the right role grants here, because the requirement says the identity must have read and write permissions to SalesDB.

The other options are incorrect:

A uses CREATE LOGIN ... FROM EXTERNAL PROVIDER, which is not the right choice for this Azure SQL Database scenario; the documented pattern is to create a database user from the external provider.

B and D create SQL-authentication principals with passwords, which does not meet the Microsoft Entra managed-identity requirement.

D also grants sysadmin, which is a server-level overgrant and not appropriate for the stated read/write requirement.

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

Comprehensive and Detailed Explanation with all Developing AI-Enabled Database Solutions documents : =

The first statement is Yes . The evidence is conclusive: the blocking session is sleeping , has open_transaction_count = 1 , and its last submitted command was BEGIN TRANSACTION UPDATE Sales.Orders . Microsoft specifically identifies a sleeping session with an open transaction as a classic blocking condition: modifications retain their exclusive locks until the transaction is committed or rolled back . The LCK_M_X waits and identical nonzero blocking_session_id values confirm that other requests are waiting for incompatible exclusive locks.

The second statement is No because READ_COMMITTED_SNAPSHOT is ON by default for SQL database in Microsoft Fabric . Under the default READ COMMITTED isolation level, reads therefore use row versioning rather than shared locks. Consequently, a normal SELECT can read the appropriate committed row version while an UPDATE holds locks, so the writer does not ordinarily block the reader. Microsoft explicitly documents RCSI as ON by default in SQL database in Microsoft Fabric .

The third statement is Yes in the context of blocking diagnostics. sys.dm_tran_locks exposes lock ownership through fields such as request_session_id , request_mode , and request_status , while sys.dm_exec_requests exposes active request and blocking_session_id information. Correlating these DMVs allows you to associate locks with sessions participating in a blocking chain. Microsoft specifically recommends sys.dm_tran_locks for determining which locks are currently held and sys.dm_exec_requests for identifying blocked and blocking sessions.

The best use case for RAG is answering user questions based on company-specific knowledge . Microsoft defines RAG as a pattern that augments a language model with a retrieval system that provides grounding data at inference time, which is exactly what you need when responses must be based on the latest transactional and reference data , must avoid retraining/fine-tuning , and should be able to include citations or references to source data.

The other options do not fit as well:

summarizing free-form user input does not inherently require retrieval from DB1,

training a custom model contradicts the requirement to avoid retraining/fine-tuning,

generating marketing slogans is a creative generation task, not a grounding-and-citation scenario. RAG is specifically strong when answers must come from your organization’s own changing knowledge.

The correct answer is B because the problem is not the vector search operator itself. The problem is that embeddings are becoming stale when article content changes . Microsoft documents that change data capture (CDC) tracks insert, update, and delete operations on source tables, which makes it the right mechanism to identify only the rows that changed.

This also best satisfies the requirement to minimize CPU usage on SalesDB . With CDC, the database only records the row changes, and the embedding regeneration work can be moved to an external process such as an Azure Functions app. That avoids running embedding generation inline inside the database for every update and avoids repeatedly recalculating embeddings for unchanged rows. In contrast, an hourly full-table regeneration would be extremely wasteful on a table with two million frequently updated articles, and a trigger that calls embedding generation per row would push expensive AI work into the transactional path of the database.

Option A is incorrect because changing from VECTOR_SEARCH to VECTOR_DISTANCE does not regenerate embeddings; it only changes the retrieval method. Microsoft states that VECTOR_SEARCH is the ANN search function, while VECTOR_DISTANCE performs exact distance calculation, so neither option addresses stale embedding data.

So the right design is:

use CDC to detect only changed articles,

process those changes outside the database,

regenerate embeddings only for changed rows,

write back the refreshed embeddings for current semantic search results.

Data API builder supports reading the database connection string from an environment variable by using the syntax:

@env( ' MSSQL_CONNECTION_STRING ' )

Microsoft’s DAB documentation explicitly shows that @env( ' MSSQL_CONNECTION_STRING ' ) tells Data API builder to read the connection string from an environment variable at runtime.

That fits this scenario because Azure Container Apps secrets are typically exposed to the container as environment variables . Microsoft’s Azure Container Apps documentation states that environment variables can reference secrets, and DAB’s Azure Container Apps deployment guidance shows a secret being mapped into an environment variable that DAB then reads.

Why the other options are wrong:

A and D incorrectly point the connection string to DAB_CONFIG_BASE64, which is the config payload secret, not the SQL connection string.

C uses secretref: syntax inside dab init, but DAB expects the connection string parameter in the config to use the environment-variable reference syntax @env(...). The secretref: pattern is for Azure Container Apps environment variable configuration, not for the DAB CLI connection-string argument itself.

So the correct command is:

dab init --database-type mssql --connection-string " @env( ' MSSQL_CONNECTION_STRING ' ) " --host-mode Production --config dab-config.json

PhoneRaw extraction → JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

PhoneNumerals → REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

WHERE condition → p.PhoneRaw

The first correct selection is JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) because the phone number is stored as a scalar JSON property . Microsoft documents that JSON_VALUE extracts a scalar value from JSON text, whereas JSON_QUERY is intended for JSON objects or arrays.

The second selection is REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) . The regular expression [^0-9] matches every character that is not a digit . Replacing all such characters with an empty string converts a formatted value such as:

+000 000-000-0000

into:

0000000000000

Microsoft documents that REGEXP_REPLACE returns the source string after occurrences matching the regular expression are replaced, and it applies to Azure SQL Database.

The final blank is p.PhoneRaw , producing:

WHERE p.PhoneRaw IS NOT NULL;

This excludes customers that do not have a phone value while preserving one output row for each customer that does.

The completed query is:

WITH PhoneCTE AS

(

SELECT DISTINCT

c.CustomerId,

JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) AS PhoneRaw

FROM dbo.Customers AS c

)

SELECT

p.CustomerId,

REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) AS PhoneNumerals

FROM PhoneCTE AS p

WHERE p.PhoneRaw IS NOT NULL;

Final drag-and-drop selections:

First target: JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

Second target: REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

Third target: p.PhoneRaw

ON → m.maintenanceId = i.maintenanceId

WHERE → m.LastModifiedUtc < > i.LastModifiedUtc

The correct drag-and-drop completion is:

ON m.maintenanceId = i.maintenanceId

WHERE m.LastModifiedUtc < > i.LastModifiedUtc

This satisfies the requirement to ensure that when a row in MaintenanceEvents changes, the corresponding LastModifiedUtc value is updated to the current system time, while also helping avoid unnecessary repeat updates.

The inserted pseudo-table in a SQL Server AFTER UPDATE trigger contains the rows that were just updated. To update the matching row in the base table correctly, the trigger must join the target table row to the corresponding row in inserted by the table’s primary key. In this schema, MaintenanceId is the primary key for MaintenanceEvents, so the correct join is m.maintenanceId = i.maintenanceId . Joining on VehicleId would be incorrect because multiple maintenance rows could exist for the same vehicle, which could update unintended rows. Microsoft’s trigger documentation explains that inserted and deleted are used to work with the affected rows and that multi-row logic should be based on proper key matching.

The WHERE m.LastModifiedUtc < > i.LastModifiedUtc predicate is used to prevent the trigger from re-updating rows where the timestamp already matches the value in inserted. That reduces redundant writes and supports the requirement to avoid recursive or repeated update behavior. In practice, this means the trigger updates only rows whose current stored timestamp differs from the just-updated version. This is the exam-appropriate pattern for a self-updating timestamp column in an AFTER UPDATE trigger.

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.

The first statement is No . The requirement says telemetry data must be stored in a partitioned table to provide predictable performance for ingestion and retention operations. However, the shown CREATE TABLE statement does not define a partition function or partition scheme, and the table is created with a regular clustered primary key on TelemetryId. Microsoft’s partitioning guidance states that creating a partitioned table requires a partition function , a partition scheme , and creating the table or index on that partition scheme using a partitioning column. None of that appears in the code, so the table is not partitioned.

The second statement is Yes . The code creates a JSON index named JI_VehicleTelemetry_Location on LocationJson for these specific JSON paths: $.location.latitude, $.location.longitude, and $.location.accuracy. That matches the requirement that those JSON properties must be filterable by using an index seek . Microsoft documents that JSON indexing is used to optimize filtering and sorting on JSON properties, and the index only helps for the properties included in the index definition.

The third statement is No . The JSON index is defined only for latitude, longitude, and accuracy. A query filtering on $.location.heading references a different path that is not included in the index definition, so that query would not use JI_VehicleTelemetry_Location for that predicate. JSON indexes are path-specific; they do not automatically cover unrelated properties in the same JSON document.

The correct column to use for generating embeddings is incidentDescrlption because embeddings are intended to represent the semantic meaning of rich textual content , not simple categorical, numeric, or location-only values. Microsoft’s DP-800 study guide explicitly includes skills such as identifying which columns to include in embeddings , generating embeddings , and implementing semantic vector search for scenarios where users need to find similar records based on meaning rather than exact matches.

In this scenario, analysts report that it is difficult to find similar incidents based on details such as weather, traffic conditions, and location . Those are descriptive context elements that are typically captured in a free-text incident description field. An embedding generated from incidentDescrlption can encode the semantic relationships among these narrative details, making it suitable for similarity search , semantic search , and RAG retrieval . Microsoft documentation on vectors and embeddings explains that embeddings are generated from text data and then stored for vector search to find semantically related items.

The other options are weaker choices:

vehicleLocation is too narrow and usually better handled with geospatial filtering , not embeddings.

incidentType is likely categorical and too low in semantic richness.

SeverityScore is numeric and not appropriate as the primary source for semantic embeddings.

Microsoft also notes that when multiple useful attributes exist, you can either embed each text column separately or concatenate relevant text fields into one textual representation before generating the embedding. But among the options given, the best and most exam-aligned answer is the textual narrative column : incidentDescrlption .

The two correct actions are D and E because the ingestion failures are caused by malformed JSON and duplicate payloads , and these two controls address those two problems directly. Microsoft’s JSON documentation states that SQL Server and Azure SQL support validating JSON with ISJSON , and Microsoft specifically recommends using a CHECK constraint to ensure JSON text stored in a column is properly formatted.

For the duplicate-payload issue, creating a unique index on a hash of the payload is the appropriate design. Microsoft documents using hashing functions such as HASHBYTES to hash column values, and SQL Server allows a deterministic computed column to be used as a key column in a UNIQUE constraint or unique index . That makes a persisted hash-based computed column plus a unique index a practical and exam-consistent way to reject duplicate payloads efficiently.

The other options do not solve the stated root causes:

Snapshot isolation addresses concurrency behavior, not malformed JSON or duplicate payload detection.

A trigger to rewrite malformed JSON is not the right integrity control and is brittle.

Foreign key constraints enforce referential integrity, not JSON validity or duplicate-payload prevention

The correct answer is D because the policy explicitly prohibits sharing customer PII, secrets, and query result sets with any AI service. The safe way to use GitHub Copilot Chat here is to provide only schema-level information such as table names, column names, relationships, and the required procedure behavior, without sharing actual table contents or result sets. That lets Copilot help generate and review the Transact-SQL while avoiding disclosure of customer data. This is consistent with Microsoft and GitHub guidance that content provided in prompts is what the AI can use, so avoiding real data in the prompt is the appropriate control.

The other options violate the requirement:

A pastes real rows containing email addresses, which is direct PII disclosure.

B shares actual query result sets, which the policy forbids.

C provides the connection string so Copilot can validate against the database, which is inappropriate because it exposes connection details and could enable access beyond schema-only assistance.

So the correct approach is to ask Copilot to generate the stored procedure using only the schema and requirements, not real customer data.

Partition range → RANGE RIGHT

Boundary values → ' 2020-01-01 00:00:00 ' , ' 2021-01-01 00:00:00 '

Comprehensive and Detailed Explanation with all Developing AI-Enabled Database Solutions documents : =

The correct configuration is to use RANGE RIGHT with boundary values at the start of each year :

CREATE PARTITION FUNCTION PartitionByYear (datetime2)

AS RANGE RIGHT

FOR VALUES (

' 2020-01-01 00:00:00 ' ,

' 2021-01-01 00:00:00 '

);

Microsoft documents that with RANGE RIGHT , each boundary value belongs to the partition on its right . For date-based partitioning, this is the natural pattern because a boundary such as 2021-01-01 becomes the lower boundary of the 2021 partition.

With these boundaries, the resulting ranges are effectively:

Partition 1: dates before 2020-01-01

Partition 2: 2020-01-01 through before 2021-01-01

Partition 3: 2021-01-01 and later

This cleanly separates the 2020 and 2021 data into year-aligned partitions. Microsoft specifically recommends RANGE RIGHT for date-based boundaries because the first day of a period remains in the same partition as the rest of that period.

The other boundary choices are incorrect or less appropriate:

Using year-end timestamps with RANGE LEFT is more cumbersome and can be sensitive to datetime2 precision.

Monthly boundaries would partition by month, not year.

Including 2019 and a 2021-12-31 23:59:59 boundary creates unnecessary partitions and is not the cleanest year-based design.

Therefore:

First dropdown: RANGE RIGHT

Second dropdown: ' 2020-01-01 00:00:00 ' , ' 2021-01-01 00:00:00 '

The best recommendation is B because the slow query filters on FleetId and returns LastUpdatedUtc , EngineStatus , and BatteryHealth . A nonclustered index with FleetId as the key column allows the optimizer to perform an index seek instead of a clustered index scan, and including the other selected columns makes the index covering , which reduces extra lookups and I/O. Microsoft’s SQL Server indexing guidance states that a nonclustered index with included columns can significantly improve performance when all query columns are available in the index, because the optimizer can satisfy the query directly from the index.

The query is:

SELECT VehicleId, LastUpdatedUtc, EngineStatus, BatteryHealth

FROM dbo.VehicleHealthSummary

WHERE FleetId = @FleetId

ORDER BY LastUpdatedUtc DESC;

Among the given choices, FleetId is the most important search argument because it appears in the WHERE predicate. Microsoft’s index design guidance recommends putting columns used for searching in the key and using nonkey included columns to cover the rest of the query efficiently.

Why the other options are weaker:

A is not appropriate because changing the clustered index to LastUpdatedUtc would not target the main filter predicate on FleetId, and a table can have only one clustered index.

C makes LastUpdatedUtc the key, which is poor for a query whose primary filter is FleetId.

D is not the right answer here because the query requirement does not specify only recent rows, and filtered indexes are meant for a well-defined subset; this option also uses a time-based expression that is not aligned to the stated query pattern.

Strictly speaking, the most optimal design for both filtering and ordering would usually be a composite key like (FleetId, LastUpdatedUtc), but since that is not one of the available options, B is the correct exam answer.