Microsoft DP-800 Exam Dumps

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.

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

To speed up repeated aggregate retrieval without changing base-table structure, the right pattern is an indexed view. Microsoft requires that an indexed view be created with WITH SCHEMABINDING, and if the view uses GROUP BY, it must also include COUNT_BIG(*). After that, the first index on the view must be a unique clustered index.

The other options fail Microsoft's indexed-view rules:

A is invalid because ORDER BY is not allowed in the indexed-view definition.

B is invalid because indexed views require WITH SCHEMABINDING, and the first index cannot just be a nonclustered index.

C is incomplete because a grouped indexed view must include COUNT_BIG(*).

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.

For an SDK-style SQL database project targeting Azure SQL Database, Microsoft recommends using the Azure SQL system DACPAC as a NuGet package reference rather than adding a direct artifact reference to master.dacpac for new SDK-style development. Microsoft's SQL Database Projects documentation says direct .dacpac artifact references are not recommended for new development in SDK-style projects; instead, use NuGet package references.

Because the goal is specifically to make dotnet build validate successfully with the correct Azure SQL system objects, adding an artifact reference to master.dacpac is not the recommended SDK-style solution. It can work in some project styles, but it does not meet the stated goal as the proper approach for SDK-style Azure SQL projects.