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

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.

Question 241

An expensive model should rerank only a small set of shortlisted search candidates rather than every document in the corpus. What is the best primary mechanism to use?

  1. Approximate top-k search
  2. Hybrid union of candidate IDs
  3. Post-retrieval reranking
  4. TOP with ordered distance

Explanation

Correct Answer

C:

A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding.

Incorrect Answers

A:

Approximate TOP with VECTOR_SEARCH can leverage supported vector indexes for low-latency ANN retrieval; it addresses a different step; the question calls for Post-retrieval reranking.

B:

Joining or unioning candidate IDs from both sources allows duplicate documents to receive contributions from multiple ranking lists; this differs from the Post-retrieval reranking requirement described in the scenario.

D:

Ordering by the computed distance ascending and selecting TOP limits results to nearest eligible vectors under exact scoring; the alternative cannot substitute for Post-retrieval reranking under the given constraints.

Question 242

A new prompt produces fluent answers that sometimes contain statements not supported by the retrieved documents, and the team needs a quality check. Which approach should the team select?

  1. Groundedness evaluation
  2. Retrieval recall benchmark
  3. Answer-latency budget
  4. Citation correctness check

Explanation

Correct Answer

A:

Groundedness evaluation compares answer claims with supplied evidence, helping flag unsupported generation.

Incorrect Answers

B:

Measuring retrieval recall on labeled questions isolates missing-evidence failures from generation mistakes; that function is not interchangeable with Groundedness evaluation for the requested result.

C:

Stage-level latency instrumentation attributes end-to-end delay to retrieval, context assembly, and generation for targeted optimization; for the stated conditions, the deciding feature is Groundedness evaluation rather than this behavior.

D:

Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links; it addresses a different step; the question calls for Groundedness evaluation.

Question 243

An RLS policy needs a schema-bound function to evaluate whether the current session's tenant may access a row. Which action is justified by these conditions?

  1. CREATE SECURITY POLICY
  2. EXECUTE AS for testing
  3. SESSION_CONTEXT
  4. Inline table-valued predicate function

Explanation

Correct Answer

D:

RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING.

Incorrect Answers

A:

CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; the required capability here is Inline table-valued predicate function, which this operation does not supply.

B:

EXECUTE AS allows supported impersonation for evaluating effective permissions and RLS behavior, without permanently changing the policy; the alternative cannot substitute for Inline table-valued predicate function under the given constraints.

C:

SESSION_CONTEXT exposes key-value session state that an RLS predicate can read, provided the application prevents identity spoofing; the condition in the stem is resolved by Inline table-valued predicate function, not by this feature alone.

Question 244

Before a release, reviewers want an XML description of the expected target schema changes without executing the deployment. What is the best primary mechanism to use?

  1. Declarative object file
  2. SqlPackage DeployReport
  3. Schema Compare
  4. SqlPackage Script

Explanation

Correct Answer

B:

DeployReport describes planned deployment changes derived from model comparison and supports approval workflows.

Incorrect Answers

A:

SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes; for the stated conditions, the deciding feature is SqlPackage DeployReport rather than this behavior.

C:

Schema Compare contrasts schema definitions from two sources and can help identify drift and reconcile them; this differs from the SqlPackage DeployReport requirement described in the scenario.

D:

SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database; the required capability here is SqlPackage DeployReport, which this operation does not supply.

Question 245

A search service is migrating to a new model dimension and must prevent old and new vectors from being silently compared as though compatible. Which database mechanism should be chosen?

  1. Versioned embedding column
  2. Update-on-change processing
  3. Delete propagation
  4. Idempotent embedding upsert

Explanation

Correct Answer

A:

Storing embedding-model identity or version with each vector permits controlled migration and query-to-corpus compatibility checks.

Incorrect Answers

B:

Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; the missing behavior is Versioned embedding column; this mechanism does something else.

C:

A deletion-aware sync process removes or invalidates vectors and searchable text tied to the erased source record; that function is not interchangeable with Versioned embedding column for the requested result.

D:

Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing; for the stated conditions, the deciding feature is Versioned embedding column rather than this behavior.

Question 246

An older sample uses deprecated TOP_N inside VECTOR_SEARCH; an engineer needs the current recommended approximate TOP form where available. What is the most appropriate design decision?

  1. Version-specific syntax review
  2. SQL Server 2025 preview indexing
  3. Exact SQL vector functions
  4. Azure SQL generally available vector index

Explanation

Correct Answer

A:

Current documentation favors SELECT TOP (N) WITH APPROXIMATE for modern indexes, while older TOP_N syntax remains a compatibility concern.

Incorrect Answers

B:

On SQL Server 2025, vector indexing and VECTOR_SEARCH have preview conditions that differ from generally available Azure SQL support; the required capability here is Version-specific syntax review, which this operation does not supply.

C:

VECTOR and VECTOR_DISTANCE provide exact vector operations separately from optional approximate index support; the alternative cannot substitute for Version-specific syntax review under the given constraints.

D:

Vector indexes and approximate VECTOR_SEARCH are generally available in Azure SQL Database, with version-specific syntax requirements; the condition in the stem is resolved by Version-specific syntax review, not by this feature alone.

Question 247

New catalog descriptions should produce embeddings asynchronously when a change-tracking-based function detects a write. Which action is justified by these conditions?

  1. AI_GENERATE_EMBEDDINGS
  2. Azure Function on change event
  3. External batching worker
  4. Database trigger with guarded work

Explanation

Correct Answer

B:

A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes.

Incorrect Answers

A:

AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; that function is not interchangeable with Azure Function on change event for the requested result.

C:

An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions; it addresses a different step; the question calls for Azure Function on change event.

D:

A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; this differs from the Azure Function on change event requirement described in the scenario.

Question 248

In a TRY/CATCH procedure, a failed transaction has entered state -1; the handler needs to detect that the transaction cannot commit or write additional log rows until it is rolled back. What is the most appropriate design decision?

  1. SAVE TRANSACTION
  2. BEGIN TRY / BEGIN CATCH
  3. THROW without arguments
  4. TRY_CONVERT
  5. XACT_STATE()

Explanation

Correct Answer

E:

XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended.

Incorrect Answers

A:

SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it; the alternative cannot substitute for XACT_STATE() under the given constraints.

B:

TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch; the condition in the stem is resolved by XACT_STATE(), not by this feature alone.

C:

THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception; the missing behavior is XACT_STATE(); this mechanism does something else.

D:

TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; that function is not interchangeable with XACT_STATE() for the requested result.

Question 249

The customer-record schema must reject any insertion where RequiredCountry is NULL, including updates that try to clear the field. Which option would you recommend?

  1. UNIQUE constraint
  2. ON DELETE CASCADE foreign key action
  3. CHECK with IN predicate
  4. NOT NULL constraint

Explanation

Correct Answer

D:

NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists.

Incorrect Answers

A:

UNIQUE enforces a candidate key distinct from the primary key and prevents duplicate non-NULL key combinations according to platform semantics; it addresses a different step; the question calls for NOT NULL constraint.

B:

A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted; this differs from the NOT NULL constraint requirement described in the scenario.

C:

A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; the required capability here is NOT NULL constraint, which this operation does not supply.

Question 250

A hosted inference integration needs independently revocable Azure permissions that are not tied to an engineer's personal login. Which option would you recommend?

  1. Database role membership
  2. Personal developer token
  3. Group-based database access
  4. Dedicated managed identity

Explanation

Correct Answer

D:

A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle.

Incorrect Answers

A:

A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals; the missing behavior is Dedicated managed identity; this mechanism does something else.

B:

A personal developer token ties service access to an individual and may require rotation if that person leaves; it is not a separate revocable workload identity.

C:

Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; for the stated conditions, the deciding feature is Dedicated managed identity rather than this behavior.

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