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

Question 590

Hundreds of reads are blocked and wait data points to an idle session holding uncommitted update locks; operators need the transaction owner. Which feature would solve the stated problem?

  1. Consistent lock ordering
  2. Enable appropriate row versioning
  3. Reduce lock footprint
  4. Find open transaction owner

Explanation

Correct Answer

D:

Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation.

Incorrect Answers

A:

A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Find open transaction owner requirement described in the scenario.

B:

READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking; the required capability here is Find open transaction owner, which this operation does not supply.

C:

Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness; the alternative cannot substitute for Find open transaction owner under the given constraints.

Question 591

A SQL-based model call should use a service principal identity token to access its Azure AI deployment without keeping a static API key. Which action is justified by these conditions?

  1. External-endpoint permission
  2. DATABASE SCOPED CREDENTIAL
  3. Managed identity authorization
  4. sp_invoke_external_rest_endpoint

Explanation

Correct Answer

C:

Managed identity authentication can authorize a supported outbound model call when the identity has the required destination role.

Incorrect Answers

A:

EXECUTE ANY EXTERNAL ENDPOINT is the relevant database permission for calling the supported external REST procedure; that function is not interchangeable with Managed identity authorization for the requested result.

B:

Database-scoped credentials support controlled outbound endpoint authentication, subject to URL, identity, and access rules; for the stated conditions, the deciding feature is Managed identity authorization rather than this behavior.

D:

sp_invoke_external_rest_endpoint invokes HTTPS endpoints with configured payload, headers, timeout, and credentials where supported; this differs from the Managed identity authorization requirement described in the scenario.

Question 592

A developer has configured the entities and wants to launch the Data API builder runtime to handle REST and GraphQL requests locally. Which choice best meets these constraints?

  1. `dab add`
  2. `dab init`
  3. DAB Redis cache
  4. `dab validate`
  5. `dab start`

Explanation

Correct Answer

E:

The start command runs the configured DAB service and hosts enabled endpoints for requests.

Incorrect Answers

A:

The add command registers an entity and its source and permissions in a DAB configuration; the alternative cannot substitute for `dab start` under the given constraints.

B:

The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; the condition in the stem is resolved by `dab start`, not by this feature alone.

C:

A supported distributed Redis cache stores response entries outside individual runtime processes for multi-instance reuse; the missing behavior is `dab start`; this mechanism does something else.

D:

The validate command checks a DAB configuration against supported validation rules without serving the application; that function is not interchangeable with `dab start` for the requested result.

Question 593

An application supplies free-text search criteria from users and must not permit those values to alter SQL command structure. Which mechanism addresses the requirement?

  1. Allowlisted MCP tools
  2. Certificate-signed SQL module
  3. Stored procedure execution boundary
  4. Restricted DAB entity permissions
  5. Parameterized SQL statements

Explanation

Correct Answer

E:

SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection.

Incorrect Answers

A:

An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement; it addresses a different step; the question calls for Parameterized SQL statements.

B:

Signing an SQL module can attach narrowly scoped certificate-associated permissions during execution without broad user privilege escalation; this differs from the Parameterized SQL statements requirement described in the scenario.

C:

Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes; the required capability here is Parameterized SQL statements, which this operation does not supply.

D:

DAB entity permissions can limit operations exposed to each role, complementing database-side permissions; the alternative cannot substitute for Parameterized SQL statements under the given constraints.

Question 594

Only the literal position of the substring 'ERROR' is needed and full regular-expression features would add unnecessary complexity. Which approach should the team select?

  1. REGEXP_MATCHES
  2. REGEXP_SUBSTR
  3. CHARINDEX
  4. REGEXP_SPLIT_TO_TABLE

Explanation

Correct Answer

C:

CHARINDEX finds the starting position of a literal substring in a string, without regex pattern semantics.

Incorrect Answers

A:

REGEXP_MATCHES returns matching occurrences as relational rows on supporting versions, unlike a single scalar extraction; the missing behavior is CHARINDEX; this mechanism does something else.

B:

REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; that function is not interchangeable with CHARINDEX for the requested result.

D:

REGEXP_SPLIT_TO_TABLE splits input text using a regular-expression delimiter and yields a tabular set of segments; it addresses a different step; the question calls for CHARINDEX.

Question 595

A DAB REST client needs just Id and Status properties and only rows whose Status is Approved. Which TWO supported query parameters should it use? Choose TWO.

  1. `dab add`
  2. `$filter`
  3. `$after`
  4. `$select`
  5. `$orderby`

Explanation

Correct Answers

B:

The DAB REST filter query parameter applies server-side row filtering according to the configured entity's capabilities; used in combination with `$select`, it handles a separate requirement of this combined workflow.

D:

The REST select parameter chooses output fields for the request and reduces unneeded projection; used in combination with `$filter`, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

The add command registers an entity and its source and permissions in a DAB configuration; the required capability here is `$filter` and `$select`, which this operation does not supply.

C:

The after parameter advances keyset-style pagination using a continuation value supplied by DAB; the condition in the stem is resolved by `$filter` and `$select`, not by this feature alone.

E:

The REST orderby parameter requests sorting by eligible exposed fields and directions; that function is not interchangeable with `$filter` and `$select` for the requested result.

Question 596

Search has returned authoritative policy excerpts and the application must synthesize a clear answer grounded in those excerpts. Which approach should the team select?

  1. Vector retrieval
  2. Answer generation
  3. Context assembly
  4. Question embedding

Explanation

Correct Answer

B:

An LLM can generate a response conditioned on retrieved evidence and the user's question, with grounding controls applied.

Incorrect Answers

A:

Vector-neighbor search ranks stored embeddings by proximity to the query representation and returns source identifiers; for the stated conditions, the deciding feature is Answer generation rather than this behavior.

C:

Context assembly selects and formats trusted retrieved passages within the token budget before model invocation; this differs from the Answer generation requirement described in the scenario.

D:

Embedding the user query with a compatible model creates the numeric representation needed for vector similarity search; the required capability here is Answer generation, which this operation does not supply.

Question 597

Trusted middleware needs to place the authenticated tenant identifier into SQL session context for a security predicate to consult. What is the right solution for this specific requirement?

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

Explanation

Correct Answer

A:

SESSION_CONTEXT exposes key-value session state that an RLS predicate can read, provided the application prevents identity spoofing.

Incorrect Answers

B:

RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING; the missing behavior is SESSION_CONTEXT; this mechanism does something else.

C:

EXECUTE AS allows supported impersonation for evaluating effective permissions and RLS behavior, without permanently changing the policy; that function is not interchangeable with SESSION_CONTEXT for the requested result.

D:

CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; for the stated conditions, the deciding feature is SESSION_CONTEXT rather than this behavior.

Question 598

A corpus update job must identify changed source IDs efficiently during routine maintenance and also fully regenerate all vectors when its model is retired. Which TWO mechanisms address these different phases? Choose TWO.

  1. Azure Functions SQL trigger
  2. Change Tracking
  3. Scheduled full rebuild
  4. Update-on-change processing
  5. Change Data Capture

Explanation

Correct Answers

B:

Change Tracking identifies changed rows and versions efficiently, making it useful for incremental re-embedding without storing full change history; used in combination with Scheduled full rebuild, it handles a separate requirement of this combined workflow.

C:

A controlled rebuild recomputes vectors across the corpus, often into a new index or version, rather than mixing incompatible embedding spaces; used in combination with Change Tracking, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

The SQL trigger binding can invoke a function for detected database changes and let the function handle inference and vector updates; this differs from the Change Tracking and Scheduled full rebuild requirement described in the scenario.

D:

Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; the condition in the stem is resolved by Change Tracking and Scheduled full rebuild, not by this feature alone.

E:

CDC records change operations and row details for configured tables, supporting event processing and replay; the missing behavior is Change Tracking and Scheduled full rebuild; this mechanism does something else.

Question 599

A query regressed after a release. The query has now finished executing; investigators need a saved operator-level plan with actual row counts from that run and archived plan versions from before the release. Which TWO diagnostics supply the records? Choose TWO.

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

Explanation

Correct Answers

A:

An actual execution plan captured for a completed execution records per-operator actual row counts and runtime observations needed to compare estimated and realized behavior.

D:

Query Store preserves eligible query texts, plans, and aggregated runtime statistics so plan changes can be correlated with regressions; used in combination with Actual execution plan, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

Query Store wait statistics can attribute supported wait categories to recorded queries, clarifying the performance bottleneck; for the stated conditions, the deciding feature is Actual execution plan and Query Store plan history rather than this behavior.

C:

Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution; it addresses a different step; the question calls for Actual execution plan and Query Store plan history.

E:

An estimated plan shows the optimizer's anticipated strategy without running the full statement and therefore lacks actual runtime counts; the required capability here is Actual execution plan and Query Store plan history, which this operation does not supply.

Question 600

A RAG corpus includes private employee notes that are not required for support-answer matching and must not enter embedding requests. Which technique applies to this scenario?

  1. Sensitive-data minimization
  2. Managed identity to Azure AI
  3. Endpoint rate-limit handling
  4. Restricted model EXECUTE rights

Explanation

Correct Answer

A:

Minimizing model inputs to approved fields limits unnecessary disclosure and reduces retention and compliance risk.

Incorrect Answers

B:

Managed identity authentication can replace static secrets for supported endpoints when the workload identity receives the required service roles; the condition in the stem is resolved by Sensitive-data minimization, not by this feature alone.

C:

Backoff, rate-limit-aware batching, and idempotent checkpoints handle transient throttling while protecting upstream model capacity; the missing behavior is Sensitive-data minimization; this mechanism does something else.

D:

Model-specific EXECUTE permissions can limit who invokes the external model, separately from SELECT on its source tables; that function is not interchangeable with Sensitive-data minimization for the requested result.

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