Microsoft DP-800 Practice Exam Questions & Answers

The correct answer is C because the current query uses VECTOR_DISTANCE('cosine', @query_vector, embedding), which performs an exact vector distance calculation. Microsoft explicitly documents that VECTOR_DISTANCE does not use a vector index, even when one exists. Exact k-nearest-neighbor searches can require scanning large numbers of vectors and calculating distances row by row, which explains the observed CPU spikes and increased semantic-search latency during peak load.

A vector index on products.embedding is specifically designed to accelerate nearest-neighbor search, and VECTOR_SEARCH is the Transact-SQL function that can use that approximate nearest-neighbor index when the index metric matches the query metric. Using METRIC = 'cosine' preserves the same similarity metric currently used by App1 while reducing the amount of computation required.

The other choices are incorrect:

A normalizes vectors but does not replace the expensive exact search with indexed ANN retrieval.

B replaces semantic vector search with lexical full-text search and changes the feature's behavior.

D a standard nonclustered B-tree index cannot accelerate vector-distance operations.

E converting embeddings to VARBINARY(8000) removes native vector semantics and does not solve the performance problem.

Therefore, the exam-aligned solution is create a vector index and use VECTOR_SEARCH with cosine distance.

This also does not meet the goal. DROP PARTITION SCHEME removes the partition scheme object from the database; it is not the command used to remove just the rows for the oldest month from a partitioned table. Microsoft's DROP PARTITION SCHEME documentation is explicit that the statement removes the partition scheme itself.

For removing only the oldest month's rows with minimal impact, Microsoft points to partition-level maintenance operations such as truncating a single partition on a partitioned table. That targets only the needed data subset and is more efficient for retention workloads.

To ensure that rerunning the CI/CD pipeline produces the same outcome without creating duplicate rows, you should use the MERGE statement with a NOT MATCHED clause. The MERGE statement is idempotent—it can safely check if data already exists using the RefID as a primary key and only insert new rows or update existing ones if needed. This approach ensures that whether the pipeline runs once or multiple times, the reference data will be consistent without duplicates. You would structure it to match on RefID and either insert (if not matched) or update (if matched) the Code, CreatedDate, and Description columns based on your business logic.

The requirement is to ensure embeddings are updated every time the underlying content changes without relying on a nightly batch job. The right design is to enable change tracking on the source table so an external process can identify which rows changed and regenerate embeddings only for those rows. Microsoft documents that change detection mechanisms are used to pick up new and updated rows incrementally, which is the right pattern when you need near-continuous refresh instead of full nightly rebuilds.

This is better than:

A . fixed-size chunking, which affects chunk strategy but not change detection.

B . a smaller embedding model, which affects model cost/latency but not update triggering.

C . table triggers, which would push embedding-maintenance logic directly into write operations and is generally not the best design for AI-processing pipelines. The question specifically asks for a solution that replaces the nightly batch requirement, not one that performs heavyweight work inline during every transaction.

The correct answer is D because the scalar UDF must return the number of whole years from the input @OrderDate to the current date/time as a single positive integer. The correct DATEDIFF order is:

DATEDIFF(year, @OrderDate, GETDATE())

Microsoft documents that DATEDIFF(datepart, startdate, enddate) returns the count of specified datepart boundaries crossed between the start and end values. Since @OrderDate is the earlier date and GETDATE() is the later date, this ordering returns a positive result for past order dates.

The other choices are incorrect:

A reverses the arguments and would return a negative value for a past order date.

B is missing RETURN, and converting month difference to years by dividing by 12 is not the direct whole-year expression the question asks for.

C subtracts year parts only, which can be off around anniversary boundaries because it ignores whether the full year has actually elapsed.

So the correct insertion at line 05 is:

RETURN DATEDIFF(year, @OrderDate, GETDATE());

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