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

Question 420

A simple read/write API needs an Orders resource mapped directly onto a supported SQL database table. Which option satisfies the desired behavior most directly?

  1. Table-backed entity
  2. Stored-procedure entity
  3. View-backed entity
  4. Explicit relationship configuration

Explanation

Correct Answer

A:

A DAB entity can map to a table source with configured permissions for REST or GraphQL operations.

Incorrect Answers

B:

DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions; it addresses a different step; the question calls for Table-backed entity.

C:

An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations; this differs from the Table-backed entity requirement described in the scenario.

D:

Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas; the required capability here is Table-backed entity, which this operation does not supply.

Question 421

A supported Azure SQL database must retrieve nearest vectors through a vector-search table expression that can use an approximate index. Which database mechanism should be chosen?

  1. VECTOR_DISTANCE
  2. VECTOR_NORMALIZE
  3. VECTORPROPERTY
  4. VECTOR_SEARCH

Explanation

Correct Answer

D:

VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form.

Incorrect Answers

A:

VECTOR_DISTANCE calculates an exact metric-specific distance and does not automatically use an approximate vector index; the condition in the stem is resolved by VECTOR_SEARCH, not by this feature alone.

B:

VECTOR_NORMALIZE produces a norm-adjusted vector for supported vector types and normalization modes; the missing behavior is VECTOR_SEARCH; this mechanism does something else.

C:

VECTORPROPERTY reads documented properties of a VECTOR value and is not itself a similarity search function; that function is not interchangeable with VECTOR_SEARCH for the requested result.

Question 422

A customer segmentation query must distribute ordered spending values into approximately four equally sized row groups. Which database mechanism should be chosen?

  1. RANK()
  2. NTILE(4)
  3. PERCENT_RANK()
  4. ROW_NUMBER()

Explanation

Correct Answer

B:

NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row.

Incorrect Answers

A:

RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; this differs from the NTILE(4) requirement described in the scenario.

C:

PERCENT_RANK produces a relative rank position rather than the discrete four approximately equal-sized buckets requested by the query.

D:

ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules; the condition in the stem is resolved by NTILE(4), not by this feature alone.

Question 423

A team wants to compare two models and must determine which actually retrieves relevant documents for its own customer-support questions. Which SQL capability is the most direct fit?

  1. Latency and throughput load test
  2. Evaluation on representative queries
  3. Smaller embedding model
  4. Larger embedding model

Explanation

Correct Answer

B:

A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name.

Incorrect Answers

A:

Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency; that function is not interchangeable with Evaluation on representative queries for the requested result.

C:

A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable; it addresses a different step; the question calls for Evaluation on representative queries.

D:

A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset; this differs from the Evaluation on representative queries requirement described in the scenario.

Question 424

A deduplication query must choose one latest record per CustomerId and assign a different ordinal to every tied row. What is the most appropriate design decision?

  1. RANK()
  2. ROW_NUMBER()
  3. NTILE(4)
  4. DENSE_RANK()

Explanation

Correct Answer

B:

ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules.

Incorrect Answers

A:

RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; the alternative cannot substitute for ROW_NUMBER() under the given constraints.

C:

NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row; the missing behavior is ROW_NUMBER(); this mechanism does something else.

D:

DENSE_RANK numbers distinct ordered values without gaps after ties within the partition; that function is not interchangeable with ROW_NUMBER() for the requested result.

Question 425

Change reviewers need human-readable T-SQL for the proposed deployment without performing any ALTER statements yet. What is the most suitable implementation?

  1. Script action
  2. Publish action
  3. DeployReport action
  4. Extract action

Explanation

Correct Answer

A:

Script produces the deployment operations as SQL text for inspection before a separately authorized execution.

Incorrect Answers

B:

Publish compares the DACPAC model with the target and performs required schema operations, with configurable deployment safeguards; this differs from the Script action requirement described in the scenario.

C:

DeployReport emits an XML description of deployment changes rather than applying them directly; the required capability here is Script action, which this operation does not supply.

D:

Extract captures a database schema into a package suitable for inspection and project initialization, without being a full table-data export; the alternative cannot substitute for Script action under the given constraints.

Question 426

A generated query targets a column removed during yesterday's deployment; the assistant's cached or supplied schema metadata is outdated. Which choice best meets these constraints?

  1. Unsafe prompt injection in data
  2. Authentication failure from tool server
  3. Stale schema context
  4. Unavailable MCP tool

Explanation

Correct Answer

C:

Refreshing schema metadata before generation or execution avoids relying on obsolete object definitions.

Incorrect Answers

A:

Data returned from a tool is lower-trust context, and its instructions must not override tool policies or reveal confidential secrets; the missing behavior is Stale schema context; this mechanism does something else.

B:

The server's database identity needs valid authentication and the necessary SQL permissions; chat access alone does not grant data access; that function is not interchangeable with Stale schema context for the requested result.

D:

Tool discovery depends on the capabilities exposed by the server; adding a prompt instruction cannot create a missing backend tool; it addresses a different step; the question calls for Stale schema context.

Question 427

Before executing a potentially costly query, an engineer wants to inspect the optimizer's proposed operators and cost estimates. What is the most appropriate design decision?

  1. Actual execution plan
  2. Estimated execution plan
  3. Query Store plan history
  4. Live query statistics

Explanation

Correct Answer

B:

An estimated plan shows the optimizer's anticipated strategy without running the full statement and therefore lacks actual runtime counts.

Incorrect Answers

A:

An actual plan records the executed query operators and runtime row counts, enabling comparison against estimates; the required capability here is Estimated execution plan, which this operation does not supply.

C:

Query Store preserves eligible query texts, plans, and aggregated runtime statistics so plan changes can be correlated with regressions; the condition in the stem is resolved by Estimated execution plan, not by this feature alone.

D:

Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution; the missing behavior is Estimated execution plan; this mechanism does something else.

Question 428

A busy database wants READ COMMITTED statements to use row versions for nonblocking committed reads without rewriting every client transaction to SNAPSHOT. What should be configured to meet this objective?

  1. READ COMMITTED locking
  2. SERIALIZABLE
  3. READ_COMMITTED_SNAPSHOT
  4. SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Explanation

Correct Answer

C:

Enabling READ_COMMITTED_SNAPSHOT changes the database's READ COMMITTED read behavior to statement-level row-version snapshots.

Incorrect Answers

A:

Locking READ COMMITTED prevents dirty reads but readers can block behind writers according to lock acquisition; for the stated conditions, the deciding feature is READ_COMMITTED_SNAPSHOT rather than this behavior.

B:

SERIALIZABLE applies stronger range-protection behavior at the cost of concurrency, especially for indexed predicates; it addresses a different step; the question calls for READ_COMMITTED_SNAPSHOT.

D:

SNAPSHOT isolation gives a version-consistent transaction-level view, unlike statement-level read-committed snapshots; the required capability here is READ_COMMITTED_SNAPSHOT, which this operation does not supply.

Question 429

An untrusted pull-request workflow should not automatically receive write permissions to secrets or deployment environments. Which technique applies to this scenario?

  1. Required status checks
  2. Pull-request template checklist
  3. Restricted workflow permissions
  4. CODEOWNERS review

Explanation

Correct Answer

C:

Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority.

Incorrect Answers

A:

Required status checks make successful automated validation a condition for merging into a protected branch; the condition in the stem is resolved by Restricted workflow permissions, not by this feature alone.

B:

A checklist can prompt reviewers but does not change the write permissions or secret access available to an untrusted workflow run.

D:

CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; for the stated conditions, the deciding feature is Restricted workflow permissions rather than this behavior.

Question 430

Each order stores a JSON items array and the query must join each extracted item back to its source OrderId. Which database mechanism should be chosen?

  1. CROSS APPLY OPENJSON
  2. FOR JSON PATH
  3. JSON_ARRAYAGG
  4. JSON_OBJECT

Explanation

Correct Answer

A:

CROSS APPLY with OPENJSON produces relational item rows correlated to their source order records.

Incorrect Answers

B:

FOR JSON PATH converts SELECT output into a path-controlled JSON representation, often suitable for API responses; the required capability here is CROSS APPLY OPENJSON, which this operation does not supply.

C:

JSON_ARRAYAGG constructs an aggregate JSON array from group members rather than returning multiple relational rows; the alternative cannot substitute for CROSS APPLY OPENJSON under the given constraints.

D:

JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; the condition in the stem is resolved by CROSS APPLY OPENJSON, not by this feature alone.

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