DP-800 Developing AI-Enabled Database Solutions Questions and Answers

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 schema meets the security requirements for PII data. → Yes

Administrators of the Azure SQL server can see all the rows in dbo.CustomerProfiles when they use an application. → No

The masking rules will apply even when row-level security (RLS) filters out rows. → No

The first statement is Yes because the design combines two relevant SQL security controls for personally identifiable information: Dynamic Data Masking (DDM) on sensitive columns such as FullName, EmailAddress, and PhoneNumber, and Row-Level Security (RLS) to restrict which rows a user can access based on RegionCode. Microsoft documents that DDM limits sensitive data exposure for nonprivileged users , while RLS restricts row access according to the user executing the query. Together, these are valid and appropriate controls for protecting PII in Azure SQL Database.

The second statement is No . Administrative users can view unmasked data because administrative roles effectively have CONTROL, which includes UNMASK. However, that does not mean they automatically see all rows through the application query path defined by the RLS policy. The security policy filters rows based on SUSER_SNAME() and matching RegionCode, so row visibility is governed by the predicate unless the policy is altered or bypassed administratively. DDM and RLS solve different problems: DDM affects how returned values are shown, while RLS affects which rows are returned at all.

The third statement is No because masking only applies to data that is actually returned in the query result set. Microsoft describes DDM as hiding sensitive data in the result set of a query . If RLS filters a row out, that row is not returned, so there is nothing left for masking to act on. In other words, RLS eliminates inaccessible rows first from the user’s perspective, and DDM masks sensitive column values only on rows the user is allowed to see.

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 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.

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 .

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.

The correct SARGable pattern for filtering a single calendar day is to use a half-open date range :

o.CreateDate > = @StartDate

AND o.CreateDate < @EndDate

with:

SET @EndDate = DATEADD(day, 1, @StartDate)

This is the correct design because it keeps the function off the column and applies it only to the parameter. That allows SQL Server and Azure SQL to use an index on CreateDate efficiently, which is the key requirement for a SARGable predicate. Microsoft documents DATEADD as the standard function for adding one day to a date value, which makes it the right way to derive the exclusive upper boundary for the next day.

The incorrect choices are the ones that wrap CreateDate in CONVERT(...), because expressions like:

CONVERT(char(10), CreateDate, 121) = ...

make the predicate non-SARGable and typically prevent efficient seeks on an index over CreateDate.

So the completed procedure is:

CREATE PROCEDURE dbo.usp_SearchOrders

@StartDate date

AS

BEGIN

SET NOCOUNT ON;

DECLARE @EndDate date;

SET @EndDate = DATEADD(day, 1, @StartDate);

SELECT o.CreateDate,

o.OrderId,

o.ShipDate

FROM dbo.Orders AS o

WHERE o.CreateDate > = @StartDate

AND o.CreateDate < @EndDate;

END;

For the vector portion, the correct choice is VECTOR_DISTANCE and order by distance ascending . The requirement is to build a combined weighted formula using the actual vector distance. Microsoft documents that VECTOR_DISTANCE returns the exact distance between two vectors. Since lower distance means greater similarity, ascending distance is the right direction for ranking. VECTOR_SEARCH is for ANN retrieval, but this hotspot specifically asks for a weighted formula based on distance , so VECTOR_DISTANCE is the appropriate operator.

For the keyword portion, the correct choice is CONTAINSTABLE on description and return ranked matches . Microsoft documents that CONTAINSTABLE returns a RANK column from 0 through 1000 , which is exactly what is needed for weighted scoring in a hybrid formula.

For the final ranking expression, the best choice is order by (distance * 0.6) + ((1.0 - RANK/1000.0) * 0.4) . This works because vector distance is a lower-is-better metric, while full-text RANK is a higher-is-better metric. Dividing RANK by 1000 normalizes it to the documented range, and subtracting from 1.0 converts it into a lower-is-better term so both components can be combined consistently in one ascending score. This final step is a sound inference based on Microsoft’s documented distance semantics and full-text rank range.

CTE inner source → FROM sys.dm_exec_requests

Join after sys.dm_exec_sessions AS s → LEFT OUTER JOIN sys.dm_exec_requests

Text retrieval → OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

Input buffer retrieval → OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

The correct drag-and-drop choices are based on how blocking-chain details are normally assembled in Azure SQL Database.

The CTE must read from sys.dm_exec_requests because the alias er is used with er.session_id and er.blocking_session_id, and those columns come from sys.dm_exec_requests. Microsoft documents that sys.dm_exec_requests returns information about executing requests and includes the blocking_session_id column used to identify blockers.

After FROM sys.dm_exec_sessions AS s, the correct join is LEFT OUTER JOIN sys.dm_exec_requests so the query can still return sessions from sys.dm_exec_sessions even when a current request row is missing. This is useful when showing sessions that are blocked or blocking, while still attempting to attach current request details when available.

For batch text, use OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) because Microsoft documents sys.dm_exec_sql_text(sql_handle) as the function that returns the SQL batch text for the specified sql_handle.

For the input buffer, use OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) because Microsoft documents that sys.dm_exec_input_buffer takes session_id and request_id and returns event_info, which is commonly used when sys.dm_exec_sql_text is null or when you want the last command text.

So the completed code uses:

FROM sys.dm_exec_requests

LEFT OUTER JOIN sys.dm_exec_requests

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

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.

Unit tests run automatically whenever changes are pushed to main. → Yes

Schema validation occurs during the Build step. → Yes

Schema validation occurs during the Deploy step. → No

The first statement is Yes . The workflow is configured to trigger on both push to main and pull_request targeting main. The unit-tests job has this condition:

if: github.ref == ' refs/heads/main '

On a push to main , GitHub sets github.ref to refs/heads/main, so the condition is true and the unit-tests job runs. GitHub’s workflow syntax documentation confirms that push.branches: [main] triggers on pushes to main, and the github.ref value for branch pushes is the fully qualified ref such as refs/heads/main.

The second statement is Yes . The Build step runs:

dotnet build db1.sqlproj --configuration Release

For an SDK-style SQL database project, the build process produces a .dacpac and validates the database project model as part of compilation/build. Microsoft’s SQL database project documentation describes SDK-style SQL projects as the project format used for SQL Database Projects, and Microsoft’s command-line build documentation is specifically about building a .dacpac from that SQL project. That means schema-level project validation happens during build.

The third statement is No . The Deploy step uses:

SqlPackage /Action:Publish ...

Microsoft documents that SqlPackage Publish incrementally updates the target database schema to match the source .dacpac. That is a deployment operation, not the primary schema-validation stage of the SQL project source itself. In this workflow, the schema is validated when the SQL project is built into the .dacpac; the deploy step applies that built artifact to the target database.

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 .

The correct function is VECTOR_SEARCH because the requirement is to perform approximate nearest neighbor (ANN) queries. Microsoft’s SQL documentation states that VECTOR_SEARCH is the function used for vector similarity search, and that an ANN index is used only with VECTOR_SEARCH when a compatible vector index exists on the target column. By contrast, VECTOR_DISTANCE calculates an exact distance and does not use a vector index for ANN retrieval.

The correct distance metric is cosine distance. Microsoft documents that VECTOR_SEARCH supports cosine, dot, and euclidean metrics, and Microsoft guidance specifically notes that cosine similarity is commonly used for text embeddings. It also states that retrieval of the most similar texts to a given text typically functions better with cosine similarity, and that Azure OpenAI embeddings rely on cosine similarity to compute similarity between a query and documents. Since both NotesEmbeddings and DescriptionEmbeddings are text-derived embeddings and the goal is to minimize the impact of different chunk sizes, cosine is the best choice because it compares direction/angle rather than being as sensitive to vector magnitude as Euclidean distance.