Microsoft DP-800 (Developing AI-Enabled Database Solutions) Practice Test 7

Question 60

A correctness benchmark must rank all filtered vectors precisely even though an ANN index is present. Which option satisfies the desired behavior most directly?

  1. VECTOR_DISTANCE exact ordering
  2. SELECT TOP (N) WITH APPROXIMATE
  3. CREATE VECTOR INDEX
  4. FORCE_ANN_ONLY hint

Explanation

Correct Answer

A:

Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results.

Incorrect Answers

B:

TOP with APPROXIMATE and an ordered vector-search distance permits eligible approximate index-based retrieval in current supported versions; it addresses a different step; the question calls for VECTOR_DISTANCE exact ordering.

C:

CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; this differs from the VECTOR_DISTANCE exact ordering requirement described in the scenario.

D:

FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query; the required capability here is VECTOR_DISTANCE exact ordering, which this operation does not supply.

Question 61

Given a system-versioned table, a reviewer needs the row state valid at one specified UTC point instead of all historical versions. Which option satisfies the desired behavior most directly?

  1. External data access
  2. Graph MATCH predicate
  3. FOR SYSTEM_TIME AS OF
  4. Ledger verification

Explanation

Correct Answer

C:

FOR SYSTEM_TIME AS OF retrieves the temporal version valid at a requested instant using the system-maintained period columns.

Incorrect Answers

A:

An external table exposes remote data through metadata and suitable connectivity without copying it into a regular local table first; the condition in the stem is resolved by FOR SYSTEM_TIME AS OF, not by this feature alone.

B:

MATCH expresses a graph traversal pattern over node and edge tables rather than a plain scalar comparison; the missing behavior is FOR SYSTEM_TIME AS OF; this mechanism does something else.

D:

Ledger verification uses generated digests and recorded history to detect tampering; it is not simply a time-travel query; for the stated conditions, the deciding feature is FOR SYSTEM_TIME AS OF rather than this behavior.

Question 62

On a supporting SQL version, a filter must determine whether a JSON document or selected path contains a specified candidate JSON value. What is the best primary mechanism to use?

  1. JSON_PATH_EXISTS
  2. JSON_CONTAINS
  3. JSON_MODIFY
  4. OPENJSON WITH

Explanation

Correct Answer

B:

JSON_CONTAINS evaluates containment semantics for JSON values rather than merely extracting a scalar for presentation.

Incorrect Answers

A:

JSON_PATH_EXISTS checks whether an SQL/JSON path exists, avoiding the need to return the path's value; this differs from the JSON_CONTAINS requirement described in the scenario.

C:

JSON_MODIFY updates content at a specified JSON path and returns the modified document expression; the alternative cannot substitute for JSON_CONTAINS under the given constraints.

D:

An OPENJSON WITH clause maps JSON paths to named typed relational columns during rowset extraction; the condition in the stem is resolved by JSON_CONTAINS, not by this feature alone.

Question 63

An invoice-processing agent must return values conforming to a machine-checked JSON schema rather than free-form prose. Which option would you recommend?

  1. Structured-output language model
  2. Multimodal model
  3. Text embedding model
  4. Multilingual embedding model

Explanation

Correct Answer

A:

A generation model with schema-constrained structured output can produce predictable field layouts for downstream validation.

Incorrect Answers

B:

A multimodal model handles supported image and text inputs together, unlike text-only embedding or generation models; for the stated conditions, the deciding feature is Structured-output language model rather than this behavior.

C:

An embedding model maps input content to numeric vectors for semantic comparison rather than producing a conversational answer; it addresses a different step; the question calls for Structured-output language model.

D:

A multilingual embedding model can represent semantically related text across supported languages in a shared vector space; this differs from the Structured-output language model requirement described in the scenario.

Question 64

An operation may drop an important column; the team must inspect actual planned schema changes before permitting release. Which action is justified by these conditions?

  1. Build once, deploy same artifact
  2. Environment approval gate
  3. Dry-run deployment script
  4. Post-release verification

Explanation

Correct Answer

C:

A generated deployment script or report exposes DacFx's intended changes without applying them during review.

Incorrect Answers

A:

Promoting one immutable DACPAC across environments preserves a traceable model artifact while target comparisons remain environment-specific; the alternative cannot substitute for Dry-run deployment script under the given constraints.

B:

Approval gates block promotion to sensitive environments until designated reviewers approve the deployment job; the condition in the stem is resolved by Dry-run deployment script, not by this feature alone.

D:

Post-deployment verification tests operational outcomes after schema changes, not merely tool exit codes; that function is not interchangeable with Dry-run deployment script for the requested result.

Question 65

Embeddings encode meaning primarily in their angle and the scorer should be insensitive to raw vector magnitude differences. Which SQL capability is the most direct fit?

  1. Euclidean distance
  2. Normalized vectors
  3. Cosine distance
  4. Dot-product distance

Explanation

Correct Answer

C:

Cosine distance evaluates angular similarity, making it useful for text embeddings where direction carries semantic signal.

Incorrect Answers

A:

Euclidean distance measures geometric distance using coordinate differences and incorporates magnitude; it addresses a different step; the question calls for Cosine distance.

B:

Normalization rescales vectors to a selected norm, making downstream distance comparisons reflect a controlled magnitude basis; this differs from the Cosine distance requirement described in the scenario.

D:

Dot-product-based vector distance applies the supported negative dot-product convention, with ordering affected by normalization and magnitudes; the alternative cannot substitute for Cosine distance under the given constraints.

Question 66

Before applying a generated database change, the pipeline should show the precise planned schema operations for approval. Which option satisfies the desired behavior most directly?

  1. SqlPackage deployment report
  2. Protected branch rule
  3. Pull-request review
  4. Database schema comparison

Explanation

Correct Answer

A:

A SqlPackage deployment report describes planned differences and supports review without immediately applying destructive changes.

Incorrect Answers

B:

Branch protection or rulesets enforce repository merge conditions independently of coding assistant recommendations; that function is not interchangeable with SqlPackage deployment report for the requested result.

C:

A reviewed pull request introduces human assessment and traceable approvals for database model changes; for the stated conditions, the deciding feature is SqlPackage deployment report rather than this behavior.

D:

Schema comparison shows differences between source models and targets, revealing unintended changes before deployment; it addresses a different step; the question calls for SqlPackage deployment report.

Question 67

A GraphQL gateway should reject an authenticated identity whose role is not allowed to invoke a mutation, before SQL changes are executed. What is the most suitable implementation?

  1. Endpoint role authorization
  2. Row-level security policy
  3. Database permission GRANT
  4. View-based projection

Explanation

Correct Answer

A:

Gateway authorization maps authenticated principals to permitted API operations and should complement database enforcement.

Incorrect Answers

B:

An RLS security policy applies row predicates at the database tier rather than depending on every client to implement a filter; the alternative cannot substitute for Endpoint role authorization under the given constraints.

C:

An object-scoped GRANT provides the minimum necessary database operation at the specified object boundary; the condition in the stem is resolved by Endpoint role authorization, not by this feature alone.

D:

Granting SELECT on a narrow view and restricting base-table access limits the visible projection when the execution and ownership model permit it; the missing behavior is Endpoint role authorization; this mechanism does something else.

Question 68

A reconciliation must find identifiers present in SourceA's result set but absent from SourceB's result set. What is the best primary mechanism to use?

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. EXCEPT

Explanation

Correct Answer

D:

EXCEPT returns distinct rows from the first input that are not found in the second input.

Incorrect Answers

A:

UNION performs set combination with duplicate elimination across compatible query projections; for the stated conditions, the deciding feature is EXCEPT rather than this behavior.

B:

UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior; it addresses a different step; the question calls for EXCEPT.

C:

INTERSECT returns distinct compatible rows appearing in both input result sets; this differs from the EXCEPT requirement described in the scenario.

Question 69

A faulty recursive traversal may loop too long and the report needs a bounded recursion depth as a safeguard. Which SQL capability is the most direct fit?

  1. MAXRECURSION query hint
  2. Nonrecursive CTE
  3. Anchor member
  4. Recursive CTE

Explanation

Correct Answer

A:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion.

Incorrect Answers

B:

A nonrecursive common table expression defines a named query expression in a WITH clause, scoped to the immediately following statement; the missing behavior is MAXRECURSION query hint; this mechanism does something else.

C:

The anchor member emits the initial rows of a recursive CTE; those rows seed later recursive expansion; that function is not interchangeable with MAXRECURSION query hint for the requested result.

D:

A recursive CTE combines anchor and recursive members to iteratively produce reachable levels of a hierarchy; for the stated conditions, the deciding feature is MAXRECURSION query hint rather than this behavior.

Question 70

Audit events need a date and clock time with configurable fractional-second precision rather than merely a calendar date. Which action is justified by these conditions?

  1. NVARCHAR(200)
  2. VARBINARY(MAX)
  3. DATETIME2
  4. DECIMAL(19,4)

Explanation

Correct Answer

C:

DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME.

Incorrect Answers

A:

NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved; this differs from the DATETIME2 requirement described in the scenario.

B:

VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding; the required capability here is DATETIME2, which this operation does not supply.

D:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; the condition in the stem is resolved by DATETIME2, not by this feature alone.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests