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

Question 230

Two otherwise similar candidates differ only because one is 2nd and the other 20th in the same component list. Which technique applies to this scenario?

  1. Dual-list presence
  2. Higher rank position
  3. Rank fusion without score calibration
  4. Reciprocal-rank contribution

Explanation

Correct Answer

B:

Reciprocal rank scoring gives a larger contribution to the higher-ranked item because its rank denominator is smaller.

Incorrect Answers

A:

RRF aggregates contributions across lists, so consistently highly ranked documents can outrank one-list specialists; this differs from the Higher rank position requirement described in the scenario.

C:

RRF works on rank positions, avoiding a need to equate arbitrary lexical and vector score magnitudes; the alternative cannot substitute for Higher rank position under the given constraints.

D:

RRF sums reciprocal-rank terms for each ranking list where a document appears, so missing-list contributions are simply absent; the condition in the stem is resolved by Higher rank position, not by this feature alone.

Question 231

A pull request introduces a foreign key referencing a nonexistent table, and the pipeline should fail before deployment. What is the best primary mechanism to use?

  1. Integration test
  2. Build-time schema validation
  3. Deployment smoke test
  4. Unit test

Explanation

Correct Answer

B:

Building a database project checks supported schema references and model consistency to catch missing dependencies.

Incorrect Answers

A:

An integration test exercises interactions among database objects and dependent components rather than one isolated expression; that function is not interchangeable with Build-time schema validation for the requested result.

C:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation; it addresses a different step; the question calls for Build-time schema validation.

D:

A unit test validates one small programmatic behavior against expected results, catching logic regressions early; this differs from the Build-time schema validation requirement described in the scenario.

Question 232

Full-text rank scores and vector distances have different scales; combining their raw numbers would be unreliable. Which technique applies to this scenario?

  1. Reciprocal-rank contribution
  2. Rank fusion without score calibration
  3. Higher rank position
  4. Dual-list presence

Explanation

Correct Answer

B:

RRF works on rank positions, avoiding a need to equate arbitrary lexical and vector score magnitudes.

Incorrect Answers

A:

RRF sums reciprocal-rank terms for each ranking list where a document appears, so missing-list contributions are simply absent; the alternative cannot substitute for Rank fusion without score calibration under the given constraints.

C:

Reciprocal rank scoring gives a larger contribution to the higher-ranked item because its rank denominator is smaller; the missing behavior is Rank fusion without score calibration; this mechanism does something else.

D:

RRF aggregates contributions across lists, so consistently highly ranked documents can outrank one-list specialists; that function is not interchangeable with Rank fusion without score calibration for the requested result.

Question 233

A stored procedure performs well for rare CustomerId values but poorly for frequent ones because the same cached plan serves highly skewed predicates. Which database mechanism should be chosen?

  1. Query Store plan forcing
  2. OPTIMIZE FOR UNKNOWN
  3. OPTION (RECOMPILE)
  4. Parameter-sensitive plan diagnosis

Explanation

Correct Answer

D:

Parameter-sensitive behavior stems from different data selectivities sharing plan assumptions; runtime and plan comparison can reveal the issue.

Incorrect Answers

A:

Query Store plan forcing can stabilize a query on a captured plan, while the DBA must monitor continued validity and suitability; it addresses a different step; the question calls for Parameter-sensitive plan diagnosis.

B:

OPTIMIZE FOR UNKNOWN directs estimation toward generalized statistics rather than a particular sniffed parameter value; this differs from the Parameter-sensitive plan diagnosis requirement described in the scenario.

C:

RECOMPILE can produce a fresh plan for the current parameter values, trading compilation cost for specialization; the required capability here is Parameter-sensitive plan diagnosis, which this operation does not supply.

Question 234

An invoice discount is allowed only from 0 to 30 percent, so cases at 0, 30, and just outside both bounds must be tested. Which technique applies to this scenario?

  1. Migration integration test
  2. Permission-negative test
  3. Positive-path unit test
  4. Boundary-value test

Explanation

Correct Answer

D:

Boundary tests target conditions at and near validation thresholds where logic defects commonly occur.

Incorrect Answers

A:

Integration tests after migration exercise live data transformations, object dependencies, and application-critical behavior; the missing behavior is Boundary-value test; this mechanism does something else.

B:

Negative authorization tests verify forbidden operations fail with the actual application principal rather than assuming intended grants; that function is not interchangeable with Boundary-value test for the requested result.

C:

Positive-path unit tests validate intended output and state changes against known inputs; for the stated conditions, the deciding feature is Boundary-value test rather than this behavior.

Question 235

One project folder uses a special migration pattern that should guide only files in that portion of the repository. Which option would you recommend?

  1. Review tool audit trail
  2. SQL style examples
  3. Least-privilege MCP tools
  4. Scoped instruction file

Explanation

Correct Answer

D:

Appropriately scoped Copilot instruction files can provide targeted coding conventions without imposing the same guidance everywhere.

Incorrect Answers

A:

Recording tool-call identities, operations, and outcomes supports accountability and incident investigation independently of conversation text; the required capability here is Scoped instruction file, which this operation does not supply.

B:

Giving concise project examples helps steer suggestions but cannot guarantee conformance without linting and review; the alternative cannot substitute for Scoped instruction file under the given constraints.

C:

Exposing a constrained metadata inspection tool reduces the actions the client can request compared with a full arbitrary-SQL tool; the condition in the stem is resolved by Scoped instruction file, not by this feature alone.

Question 236

A report should include only invoice lines that successfully match a valid invoice header by key. Which database mechanism should be chosen?

  1. LEFT JOIN
  2. CROSS APPLY
  3. INNER JOIN
  4. OUTER APPLY

Explanation

Correct Answer

C:

An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate.

Incorrect Answers

A:

A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches; for the stated conditions, the deciding feature is INNER JOIN rather than this behavior.

B:

CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions; it addresses a different step; the question calls for INNER JOIN.

D:

OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one; the required capability here is INNER JOIN, which this operation does not supply.

Question 237

The same system should retrieve nearby descriptions for symptom paraphrases independent of exact token overlap. Which mechanism addresses the requirement?

  1. Vector candidate pipeline
  2. Lexical candidate pipeline
  3. Final document authorization check
  4. RRF fusion stage

Explanation

Correct Answer

A:

Vector-neighbor retrieval produces semantic candidate IDs and distance-based order for the second fusion input.

Incorrect Answers

B:

Full-text predicates or table-valued search functions can produce lexical matches and optional rank values as one fusion input; the missing behavior is Vector candidate pipeline; this mechanism does something else.

C:

Authorization-aware source hydration prevents relevant-but-private candidate rows from becoming unauthorized LLM context; that function is not interchangeable with Vector candidate pipeline for the requested result.

D:

RRF converts each result rank into reciprocal contributions and sums them across available lists; for the stated conditions, the deciding feature is Vector candidate pipeline rather than this behavior.

Question 238

A self-hosted Azure runner should reach SQL deployment resources using its assigned workload identity where supported. Which option satisfies the desired behavior most directly?

  1. OpenID Connect federation
  2. Least-privilege deployment principal
  3. Dry-run deployment script
  4. Managed identity in hosted runner
  5. Key Vault secret reference

Explanation

Correct Answer

D:

A managed identity provides an Azure-hosted workload with token-based access instead of a persisted username and password.

Incorrect Answers

A:

OIDC workload identity federation lets a trusted workflow exchange a short-lived identity token for authorized Azure credentials; this differs from the Managed identity in hosted runner requirement described in the scenario.

B:

A narrowly scoped deployment identity limits damage and requires only appropriate SQL and Azure resource permissions for the target workflow; the required capability here is Managed identity in hosted runner, which this operation does not supply.

C:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; the alternative cannot substitute for Managed identity in hosted runner under the given constraints.

E:

Fetching secrets from Key Vault centralizes storage and access control, while the pipeline should avoid logging returned values; the missing behavior is Managed identity in hosted runner; this mechanism does something else.

Question 239

Developers and reviewers need a diffable inventory of approved configuration codes alongside schema changes. Which mechanism addresses the requirement?

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

Explanation

Correct Answer

B:

Keeping static values in source control preserves review history, though a deployment step is still needed to apply them.

Incorrect Answers

A:

An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; that function is not interchangeable with Version-controlled reference CSV for the requested result.

C:

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 Version-controlled reference CSV.

D:

Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow; this differs from the Version-controlled reference CSV requirement described in the scenario.

Question 240

Ten analysts need identical table-level SELECT grants and changes should be administered at one shared membership location. Which database mechanism should be chosen?

  1. Database role membership
  2. Certificate-signed SQL module
  3. Group-based database access
  4. Dedicated managed identity

Explanation

Correct Answer

A:

A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals.

Incorrect Answers

B:

Signing an SQL module can attach narrowly scoped certificate-associated permissions during execution without broad user privilege escalation; the condition in the stem is resolved by Database role membership, not by this feature alone.

C:

Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; the missing behavior is Database role membership; this mechanism does something else.

D:

A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle; that function is not interchangeable with Database role membership for the requested result.

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