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

Question 370

Two retrieval systems emit numeric scores on different scales and the team wants a carefully calibrated weighted blend. What is the most appropriate design decision?

  1. Keyword-only ranking
  2. Reciprocal Rank Fusion
  3. Weighted score normalization
  4. Vector-only ranking

Explanation

Correct Answer

C:

Normalizing and weighting scores can combine modalities, but requires understanding score distributions and calibration.

Incorrect Answers

A:

Lexical ranking rewards occurrences of specified terms, supporting exact identifiers that embeddings may not distinguish reliably; the missing behavior is Weighted score normalization; this mechanism does something else.

B:

RRF adds reciprocal contributions based on each result's rank position, making lexical and vector rankings comparable without raw-score calibration; that function is not interchangeable with Weighted score normalization for the requested result.

D:

Vector-only ranking uses embedding proximity and may miss exact special identifiers that lexical retrieval handles better; it addresses a different step; the question calls for Weighted score normalization.

Question 371

After a questionable answer, compliance reviewers must reconstruct which documents and prompt version informed the result. What is the most appropriate design decision?

  1. Audit retrieved sources
  2. Retry transient model throttling
  3. Redact confidential fields
  4. Limit retrieved context

Explanation

Correct Answer

A:

Logging approved source identifiers and prompt metadata creates a traceable record for investigation without storing unrestricted sensitive content.

Incorrect Answers

B:

Rate-limit-aware retries and idempotent request handling reduce failures without overwhelming the inference service; the alternative cannot substitute for Audit retrieved sources under the given constraints.

C:

Redaction and data minimization remove unnecessary secrets or identifiers before leaving the SQL trust boundary; the condition in the stem is resolved by Audit retrieved sources, not by this feature alone.

D:

Limiting and ranking context passages keeps input within token budgets and reduces irrelevant material sent to the generator; the missing behavior is Audit retrieved sources; this mechanism does something else.

Question 372

A legacy integration must store arbitrary binary document bytes directly in a database column, not a path or an encoded textual representation. Which mechanism addresses the requirement?

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

Explanation

Correct Answer

D:

VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding.

Incorrect Answers

A:

NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved; for the stated conditions, the deciding feature is VARBINARY(MAX) rather than this behavior.

B:

DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME; it addresses a different step; the question calls for VARBINARY(MAX).

C:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; this differs from the VARBINARY(MAX) requirement described in the scenario.

Question 373

A plan performs thousands of key lookups for just two columns missing from a selective nonclustered index. Which action is justified by these conditions?

  1. Read/write index tradeoff
  2. Covering index evaluation
  3. Histogram selectivity
  4. Recompile after schema change

Explanation

Correct Answer

B:

Adding appropriate INCLUDE columns can make the access path covering and reduce repetitive base-row lookups, if write overhead is acceptable.

Incorrect Answers

A:

Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; the condition in the stem is resolved by Covering index evaluation, not by this feature alone.

C:

Statistics histograms describe value distributions used to estimate predicate cardinality and influence operator selection; that function is not interchangeable with Covering index evaluation for the requested result.

D:

Plan recompilation allows the optimizer to select operators using current metadata and available statistics; for the stated conditions, the deciding feature is Covering index evaluation rather than this behavior.

Question 374

A windowed ranking should restart from 1 for each customer rather than ranking all customers' transactions together. Which mechanism addresses the requirement?

  1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  2. ORDER BY EventTime in OVER clause
  3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  4. PARTITION BY CustomerId

Explanation

Correct Answer

D:

PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact.

Incorrect Answers

A:

This ROWS window frame contains at most the current row and two prior ordered rows for moving calculations; this differs from the PARTITION BY CustomerId requirement described in the scenario.

B:

ORDER BY inside OVER establishes the function's logical row ordering; the outer query's ORDER BY controls presentation separately; the required capability here is PARTITION BY CustomerId, which this operation does not supply.

C:

A ROWS frame defined this way forms a growing row-based window and gives predictable cumulative aggregation with a deterministic ORDER BY; the alternative cannot substitute for PARTITION BY CustomerId under the given constraints.

Question 375

A complex view needs custom logic to translate an attempted INSERT into changes in its underlying tables. What is the right solution for this specific requirement?

  1. AFTER trigger
  2. DEFAULT constraint
  3. INSTEAD OF trigger
  4. CHECK constraint

Explanation

Correct Answer

C:

An INSTEAD OF trigger substitutes custom DML processing for the triggering operation, often making certain views writable.

Incorrect Answers

A:

An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; that function is not interchangeable with INSTEAD OF trigger for the requested result.

B:

A DEFAULT constraint supplies an omitted insertion value and is simpler than an event-triggered procedural program; for the stated conditions, the deciding feature is INSTEAD OF trigger rather than this behavior.

D:

A CHECK constraint is a declarative integrity rule enforced by the engine when qualifying rows are inserted or updated; this differs from the INSTEAD OF trigger requirement described in the scenario.

Question 376

A tenant must be prevented from INSERTing or UPDATEing a row tagged with another tenant's identifier. What should be configured to meet this objective?

  1. Masked column definition
  2. RLS filter predicate
  3. RLS block predicate
  4. GRANT SELECT on view

Explanation

Correct Answer

C:

An RLS block predicate can reject changes that would violate a row-access policy during specified DML operations.

Incorrect Answers

A:

A masking function on the column changes the result shown to principals lacking UNMASK, leaving the underlying value unchanged; the alternative cannot substitute for RLS block predicate under the given constraints.

B:

An RLS filter predicate limits visible rows for eligible operations at the table's security-policy boundary; the condition in the stem is resolved by RLS block predicate, not by this feature alone.

D:

Object-level SELECT permissions can be granted on the view while base-table privileges remain constrained, with ownership chaining considered; that function is not interchangeable with RLS block predicate for the requested result.

Question 377

A database-backed REST API must protect customer data crossing public network hops against passive interception. Which database mechanism should be chosen?

  1. Managed identity to database
  2. Validate JWT audience
  3. TLS termination
  4. Per-role API operation policy

Explanation

Correct Answer

C:

TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations.

Incorrect Answers

A:

A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately; it addresses a different step; the question calls for TLS termination.

B:

Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; this differs from the TLS termination requirement described in the scenario.

D:

Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization; the alternative cannot substitute for TLS termination under the given constraints.

Question 378

A reference-data script may run repeatedly and must not create another identical currency-code row on each deployment. Which choice best meets these constraints?

  1. Post-deployment seed script
  2. Version-controlled reference CSV
  3. Idempotent MERGE or upsert logic
  4. Integration test for referential values

Explanation

Correct Answer

C:

An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases.

Incorrect Answers

A:

Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow; the missing behavior is Idempotent MERGE or upsert logic; this mechanism does something else.

B:

Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; that function is not interchangeable with Idempotent MERGE or upsert logic for the requested result.

D:

A targeted integration or smoke test validates that required reference records are present and usable by related constraints; it addresses a different step; the question calls for Idempotent MERGE or upsert logic.

Question 379

A service allows anonymous catalog reads but must prevent the same callers from deleting products or accessing pricing mutations. Which choice best meets these constraints?

  1. Managed identity to database
  2. TLS termination
  3. Per-role API operation policy
  4. Validate JWT audience

Explanation

Correct Answer

C:

Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization.

Incorrect Answers

A:

A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately; the required capability here is Per-role API operation policy, which this operation does not supply.

B:

TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; the alternative cannot substitute for Per-role API operation policy under the given constraints.

D:

Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; the missing behavior is Per-role API operation policy; this mechanism does something else.

Question 380

A release needs to insert or update a few stable ISO country-code rows after schema changes without treating business transactions as source code. Which action is justified by these conditions?

  1. Version-controlled reference CSV
  2. Integration test for referential values
  3. Post-deployment seed script
  4. Idempotent MERGE or upsert logic

Explanation

Correct Answer

C:

Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow.

Incorrect Answers

A:

Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; for the stated conditions, the deciding feature is Post-deployment seed script rather than this behavior.

B:

A targeted integration or smoke test validates that required reference records are present and usable by related constraints; it addresses a different step; the question calls for Post-deployment seed script.

D:

An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; the required capability here is Post-deployment seed script, which this operation does not supply.

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