サンプル問題 1： 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?
A: Export the database schema as a .dacpac file and load the schema into a GitHub Copilot context window.
B: Add the schema to a GitHub Copilot instruction file.
C: Use an MCP server
D: Include the database project in the code repository.
正解：C
Explanation: 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.
サンプル問題 2： You need to recommend a solution that will resolve the ingestion pipeline failure issues. Which two actions should you recommend? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
A: Enable snapshot isolation on the database.
B: Use a trigger to automatically rewrite malformed JSO
C: Add foreign key constraints on the table.
D: Create a unique index on a hash of the payload.
E: Add a check constraint that validates the JSON structure.
正解：C, D
Explanation: 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
サンプル問題 3： You need to recommend a solution to lesolve the slow dashboard query issue. What should you recommend?
A: Create a clustered index on Lastupdatedutc.
B: On Fleetid, create a nonclustered index that includes Lastupdatedutc. inginestatus, and BatteryHealth.
C: On Lastupdatedutc. create a nonclustered index that includes Fleetid.
D: On Fleetid, create a filtered index where lastupdatedutc > DATEADD(DAV, -7, SYSuTCOATETIME()).
正解：B
Explanation: 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.
DP-800問題集による問題演習は、Microsoft Certified: SQL AI Developer試験の復習に利用できます。参考書を読むだけでなく、実際に問題を解くことで理解度を確認できます。
