Free Microsoft Developing AI-Enabled Database Solutions DP-800 Exam Questions

In Visual Studio Code, repository-scoped MCP server configurations are stored in the .vscode/settings.json file. To ensure GitHub Copilot Chat can call the GitHub MCP Server using OAuth with repository-level scoping, you must add the MCP server configuration to this file. This configuration file is stored in the repository's .vscode directory, making it repository-specific, and allows you to define the OAuth authentication method required for the MCP server connection.

To normalize email addresses by removing subaddressing (the + and everything after it but before the @), you need to extract the email from the JSON Payload, find the position of the '+' and '@' characters, and reconstruct the email without the subaddressing portion. The expression extracts the email_value using JSON_VALUE, locates the '+' symbol, removes everything from the '+' to just before the '@', and reconstructs the email. For example, 'user1+promo@contoso.com' becomes 'user1@contoso.com'. A more concise approach would use REPLACE or SUBSTRING functions combined with CHARINDEX to locate and remove the subaddressing part of the email string.

GitHub documents that repository custom instructions for Copilot Chat can be disabled for your own use in the editor settings, and that doing so does not affect other users. In VS Code, this is controlled through settings related to instruction files, where you can disable the use of repository instruction files for your own environment.

The other options are incorrect:

B is not a documented mechanism for disabling repository-level Copilot instructions.

C would remove the repository instruction file itself and therefore affect everyone using that repository, which violates the requirement.

According to GitHub Copilot instruction precedence rules, the hierarchy from highest to lowest precedence is: personal instructions, agent-specific instructions, repository-wide instructions, and organization-wide instructions. Therefore, agent-specific instructions take the highest precedence among all levels. Personal instructions would only apply if they exist for the individual user, but in the context of comparing the different scope levels mentioned (organization, repository, agent, and personal), agent-specific instructions have the highest precedence in the evaluation chain.

This solution does NOT meet the goal. Dropping a partition scheme would drop the entire partitioning structure for the table, not just remove rows from a specific partition. Additionally, you cannot drop a partition scheme while a table is using it. The correct approach is to use MERGE or TRUNCATE TARGET for the specific partition. For SQL databases with partitioned tables, you should identify the partition number for the oldest month and use ALTER TABLE...DROP PARTITION to remove that partition, or use a DELETE statement with a WHERE clause targeting that month's data, or use TRUNCATE TABLE with PARTITION to remove only the oldest partition's data. Simply dropping the partition scheme would remove the partitioning entirely and could cause significant disruption to the table and queries.

When you change embedding models, the stored vectors should be treated as belonging to a different embedding space unless you intentionally keep the entire corpus consistent. Microsoft's vector guidance notes that when most or all embeddings are replaced with fresh embeddings from a new model, the recommended practice is to reload the new embeddings and, for large-scale replacement scenarios, consider dropping and recreating the vector index afterward so search quality remains predictable.

This question also says applications must continue to use VECTOR_SEARCH without runtime errors. VECTOR_SEARCH requires compatible vector dimensions, and the vector column already exists. Azure OpenAI documentation shows that text-embedding-ada-002 is fixed at 1536 dimensions and text-embedding-3-small supports up to 1536 dimensions. That means the migration can remain compatible with a VECTOR(1536) column, but the right implementation step is still to re-embed the existing rows so the table does not contain a mixed corpus produced by different models.

The other options are not appropriate:

B normalization does not solve a model migration problem.

C converting the vector column to nvarchar(max) would break vector-native search design.

D a vector index improves performance, but it does not migrate old embeddings to the new model.

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.

Because the SQL Server 2025 instance has no outbound network connectivity, the embedding model cannot rely on a remote REST endpoint such as Azure AI Foundry or Azure OpenAI. Microsoft's CREATE EXTERNAL MODEL documentation includes a local deployment pattern using ONNX Runtime running locally with local runtime/model paths. That is the right design when embeddings must be generated inside the SQL Server instance without external network access. Microsoft explicitly documents a local ONNX Runtime example for SQL Server 2025 and notes the required local runtime setup and model path configuration.

The permission requirement is handled by granting the application user access to use the external embeddings model. Microsoft's AI_GENERATE_EMBEDDINGS documentation states that, as a prerequisite, you must create an external model of type EMBEDDINGS that is accessible via the correct grants, roles, and/or permissions. Among the choices, the exam-appropriate action is to grant execute permission on the external model project to AlApplicationUser so only that database user can run embedding generation through the model.

This does not meet the goal. A row-by-row DELETE against the oldest month is not the lowest-impact way to purge data from a monthly partitioned table. Microsoft's partitioning guidance specifically says partitioning lets you perform maintenance and retention operations more efficiently by targeting just the relevant partition, including the ability to truncate data in a single partition.

The proposed statement:

DELETE FROM dbo.Orders WHERE OrderDate < DATEADD(month, -36, SYSUTCDATETIME());

would log row deletions and can hold locks longer, creating more overhead for other queries than a partition-level maintenance operation. Since the table is already partitioned by month, the expected low-impact approach is to operate on the oldest partition directly, not issue a broad delete predicate over rows. Microsoft explicitly highlights partition-targeted truncation as a faster, more efficient retention operation than working against the whole table or rowset.

The correct query is Option D because it starts from the input person and uses exactly two directed Knows edges in a single MATCH pattern:

MATCH(p1-(k1)->p2-(k2)->p3)

Microsoft documents that SQL Graph uses the MATCH predicate in the WHERE clause to express graph traversal patterns over node and edge tables, and directed relationships are written with arrow syntax such as node1-(edge)->node2.

Why D is correct:

It anchors the starting node with p1.PersonId = @StartPersonId.

It traverses two directed hops: p1 -> p2 -> p3.

It returns p3.PersonId, p3.DisplayName, which are the people reachable in exactly two Knows relationships.

Why the others are wrong:

A filters on DisplayName = DisplayName, which is unrelated to the required input parameter and does not correctly anchor the start node.

B reverses the traversal direction in the pattern.

C uses two separate MATCH predicates instead of the required single two-hop directed pattern. The proper graph pattern syntax supports chaining the hops directly in one MATCH expression.