Microsoft Developing AI-Enabled Database Solutions DP-800 Exam Dumps: Updated Questions & Answers (September 2026)

JSON_VALUE(f.FeedbackJson, ' $.text ' ) AS FeedbackText

CONTAINS(FeedbackJson, @Keyword)

SimilarityScore

These three selections are the correct way to complete the query because they align exactly with the stated requirements for the FeedbackJson column.

First, to extract the customer feedback text from the JSON document , the correct expression is JSON_VALUE(f.FeedbackJson, ' $.text ' ) AS FeedbackText . Microsoft documents that JSON_VALUE is used to extract a scalar value from JSON, while JSON_QUERY is used for returning an object or array . Since $.text is the textual feedback string, JSON_VALUE is the correct function.

Second, to filter rows where the JSON text contains a keyword , the best choice is CONTAINS(FeedbackJson, @Keyword) . The scenario explicitly states that FeedbackJson already has a full-text index , and Microsoft documents that CONTAINS is the full-text predicate used in the WHERE clause to search full-text indexed character data. That makes it more appropriate than using EDIT_DISTANCE for keyword filtering.

Third, to order the results by similarity score, highest first , the correct item is SimilarityScore in the ORDER BY clause, which would be paired with DESC in the query. This matches the requirement to sort by the computed fuzzy similarity value. The DP-800 study guide specifically includes writing queries that use fuzzy string matching functions such as EDIT_DISTANCE, which supports the earlier computed SimilarityScore expression in the query.

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

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

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 Option B because the requirement is to call an Azure OpenAI REST endpoint from SQL Server 2025 while providing the highest level of security , and the instance already has a managed identity enabled . For Microsoft’s SQL AI features, the preferred secure pattern is to use a database scoped credential with IDENTITY = ' Managed Identity ' instead of storing an API key. Microsoft documents that SQL Server 2025 supports managed identity for external AI endpoints, and for Azure OpenAI the credential secret uses the Cognitive Services resource identifier: { " resourceid " : " https://cognitiveservices.azure.com " } .

So line 02 should be:

WITH IDENTITY = ' Managed Identity ' ,

SECRET = ' { " resourceid " : " https://cognitiveservices.azure.com " } ' ;

Why the other options are incorrect:

A and D use HTTP header or query-string credentials with an API key , which is less secure than managed identity because a secret key must be stored and rotated manually. Microsoft recommends managed identity where supported to avoid embedded secrets.

C mixes Managed Identity with an api-key secret, which is not the correct pattern for Azure OpenAI managed-identity authentication.

E uses an invalid identity value for this scenario. The accepted credential identities for external REST endpoint calls include HTTPEndpointHeaders , HTTPEndpointQueryString , Managed Identity , and Shared Access Signature .

Because the endpoint is Azure OpenAI and the question explicitly asks for the highest security , managed identity with the Cognitive Services resource ID is the Microsoft-aligned answer.

Verified Answer : =

queryType → " semantic "

semanticConfiguration → " hotels "

captions → " extractive "

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

The correct configuration is queryType: " semantic " , semanticConfiguration: " hotels " , and captions: " extractive " .

The request is already a hybrid search because it contains both a textual search value ( " ocean view " ) and a vectorQueries block targeting DescriptionVector . Microsoft documents that hybrid search executes full-text and vector searches together and merges their result sets. When semantic ranking is required, queryType must be set to " semantic " .

The semanticConfiguration value must reference the semantic configuration defined for the target index. Since the index in this question is named hotels and the available answer choice is " hotels " , that is the appropriate configuration value in the supplied answer set. Microsoft’s REST examples show that semantic queries require both queryType: " semantic " and a valid semanticConfiguration .

To return semantic captions, use " captions " : " extractive " . Microsoft states that extractive captions identify the most relevant passages from the indexed content and return them with each result. They are generated from existing document text rather than generated by an LLM.

The completed request body is therefore:

{

" search " : " ocean view " ,

" queryType " : " semantic " ,

" semanticConfiguration " : " hotels " ,

" captions " : " extractive " ,

" top " : 10,

" hybridSearch " : {

" maxTextRecallSize " : 50

},

" vectorQueries " : [

{

" kind " : " vector " ,

" vector " : [ /* embedding array */ ],

" fields " : " DescriptionVector " ,

" k " : 50

}

]

}

Final drag-and-drop selections:

First target: " semantic "

Second target: " hotels "

Third target: " extractive "

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 .

The first statement is No . GETDATE() is a nondeterministic built-in function, and SQL Server UDF guidance does not support using nondeterministic built-ins inside a T-SQL user-defined function for this purpose. Microsoft explicitly classifies GETDATE() as nondeterministic.

The second statement is Yes . Multi-statement TVFs are a common source of weak cardinality estimates and unstable plans because the optimizer has less information about their result sizes. Microsoft documents features such as interleaved execution for multi-statement TVFs , which exist specifically because MSTVFs have optimization challenges, and Microsoft also notes that scalar/UDF style constructs can lead to poorer optimization behavior than relationally inlined alternatives. Rewriting an MSTVF as an inline TVF generally gives the optimizer better visibility and more consistent plans.

The third statement is Yes . Scalar functions must be invoked using at least the two-part name < schema > . < function > . Microsoft’s documentation is explicit on that point for executing user-defined functions. So changing ufn_GetTaxMultiplier to dbo.ufn_GetTaxMultiplier resolves the naming error in the EXEC statement.