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

Question 180

One transaction was selected as a deadlock victim during a correct concurrent design; the application must recover safely from a transient victim error. Which option satisfies the desired behavior most directly?

  1. Optimistic rowversion check
  2. Deadlock retry policy
  3. Atomic transaction
  4. Pessimistic transaction locking

Explanation

Correct Answer

B:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects.

Incorrect Answers

A:

Comparing the original ROWVERSION token in UPDATE's WHERE clause allows a zero-row result to signal a stale write; for the stated conditions, the deciding feature is Deadlock retry policy rather than this behavior.

C:

A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior; this differs from the Deadlock retry policy requirement described in the scenario.

D:

Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step; the required capability here is Deadlock retry policy, which this operation does not supply.

Question 181

An AI assistant suggests granting db_owner to fix a permission error; the DBA must decide if the suggestion violates least privilege. Which option satisfies the desired behavior most directly?

  1. Review generated query plans
  2. Validate against target SQL dialect
  3. Review security privileges
  4. Run unit and integration tests

Explanation

Correct Answer

C:

Effective permission analysis identifies the required object-level rights rather than assuming broad database ownership is necessary.

Incorrect Answers

A:

Reviewing actual execution plans and representative workload metrics reveals access paths and cardinality issues that text generation alone cannot verify; the condition in the stem is resolved by Review security privileges, not by this feature alone.

B:

Compiling and testing against the actual target platform identifies unsupported syntax or version-specific behaviors that prompt text cannot guarantee; the missing behavior is Review security privileges; this mechanism does something else.

D:

Automated tests exercise database contracts and interactions, detecting regressions that a plausible generated explanation may conceal; for the stated conditions, the deciding feature is Review security privileges rather than this behavior.

Question 182

A synchronization client mainly needs to know which primary-key rows changed since a stored version and can re-read current values. Which SQL capability is the most direct fit?

  1. Azure Functions SQL trigger
  2. Change event streaming
  3. Change Data Capture
  4. Change Tracking

Explanation

Correct Answer

D:

Change Tracking maintains lightweight change versions and keys instead of full historical before-and-after values.

Incorrect Answers

A:

The SQL trigger binding can drive Azure Functions from supported SQL change-tracking signals, subject to its trigger configuration; this differs from the Change Tracking requirement described in the scenario.

B:

Change event streaming sends supported database change events to a configured streaming destination, with platform-specific prerequisites; the required capability here is Change Tracking, which this operation does not supply.

C:

CDC captures data changes in change tables for later consumption, including row images and operation information subject to configuration; the alternative cannot substitute for Change Tracking under the given constraints.

Question 183

A temporary GRANT is no longer needed and administrators want to remove that direct permission assignment without adding a new explicit prohibition. Which option would you recommend?

  1. GRANT SELECT on schema
  2. REVOKE permission
  3. DENY permission
  4. GRANT EXECUTE

Explanation

Correct Answer

B:

REVOKE removes a previously granted or denied permission entry and allows remaining permissions to determine effective access.

Incorrect Answers

A:

Schema-level SELECT grants apply to eligible objects within the specified schema scope, reducing per-object grant administration; that function is not interchangeable with REVOKE permission for the requested result.

C:

DENY explicitly prevents the named permission, subject to SQL Server's permission-resolution behavior and exceptions; it addresses a different step; the question calls for REVOKE permission.

D:

EXECUTE allows a principal to invoke a stored procedure and can be paired with safe module ownership or execution-context design; this differs from the REVOKE permission requirement described in the scenario.

Question 184

Developers must compare average duration and CPU for the same query in two release intervals, not just examine one execution. Which approach should the team select?

  1. Query Store runtime statistics
  2. Live query statistics
  3. Force known good query plan
  4. Query Performance Insight
  5. Query Store wait statistics

Explanation

Correct Answer

A:

Query Store runtime aggregation preserves historical performance measures across collection windows.

Incorrect Answers

B:

Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution; the condition in the stem is resolved by Query Store runtime statistics, not by this feature alone.

C:

Query Store plan forcing can direct SQL Server toward a previously captured plan while conditions still permit its use; the missing behavior is Query Store runtime statistics; this mechanism does something else.

D:

Query Performance Insight surfaces query performance trends in Azure SQL to help locate high-impact workloads without a separate profiler trace; that function is not interchangeable with Query Store runtime statistics for the requested result.

E:

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

Question 185

The table already has a suitable tenant filter function, and the DBA must attach that function as an enforceable row-access rule. Which approach should the team select?

  1. CREATE SECURITY POLICY
  2. SESSION_CONTEXT
  3. Row-level security policy
  4. Inline table-valued predicate function
  5. EXECUTE AS for testing

Explanation

Correct Answer

A:

CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled.

Incorrect Answers

B:

SESSION_CONTEXT exposes key-value session state that an RLS predicate can read, provided the application prevents identity spoofing; this differs from the CREATE SECURITY POLICY requirement described in the scenario.

C:

An RLS security policy applies row predicates at the database tier rather than depending on every client to implement a filter; the required capability here is CREATE SECURITY POLICY, which this operation does not supply.

D:

RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING; the alternative cannot substitute for CREATE SECURITY POLICY under the given constraints.

E:

EXECUTE AS allows supported impersonation for evaluating effective permissions and RLS behavior, without permanently changing the policy; the condition in the stem is resolved by CREATE SECURITY POLICY, not by this feature alone.

Question 186

A production schema repository must block direct commits to main while the team enforces required review and check policies. Which choice best meets these constraints?

  1. SqlPackage deployment report
  2. Pull-request review
  3. Protected branch rule
  4. Database schema comparison

Explanation

Correct Answer

C:

Branch protection or rulesets enforce repository merge conditions independently of coding assistant recommendations.

Incorrect Answers

A:

A SqlPackage deployment report describes planned differences and supports review without immediately applying destructive changes; the missing behavior is Protected branch rule; this mechanism does something else.

B:

A reviewed pull request introduces human assessment and traceable approvals for database model changes; that function is not interchangeable with Protected branch rule for the requested result.

D:

Schema comparison shows differences between source models and targets, revealing unintended changes before deployment; it addresses a different step; the question calls for Protected branch rule.

Question 187

A coding assistant is allowed to explore a production SQL schema but not mutate it without review. Which TWO enforceable controls should limit its initial access and sensitive actions? Choose TWO.

  1. Secret scanning in GitHub
  2. Redaction of sensitive prompt context
  3. Chat tool permissions
  4. Human approval before tool execution
  5. Read-only database principal

Explanation

Correct Answers

D:

Requiring human confirmation for sensitive tool calls reduces the risk of autonomous changes and maintains an accountable approval point; used in combination with Read-only database principal, it handles a separate requirement of this combined workflow.

E:

A read-only database principal with narrowly scoped SELECT access limits potential writes at the database authorization boundary; used in combination with Human approval before tool execution, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

Secret scanning can identify supported credential patterns in repositories and complements, rather than replaces, credential rotation; the required capability here is Human approval before tool execution and Read-only database principal, which this operation does not supply.

B:

Removing or masking confidential fields before prompt submission minimizes unnecessary exposure through an AI-assisted workflow; the alternative cannot substitute for Human approval before tool execution and Read-only database principal under the given constraints.

C:

Tool permission and approval settings control reachable actions in the assistant session and need restriction before permitting database access; the condition in the stem is resolved by Human approval before tool execution and Read-only database principal, not by this feature alone.

Question 188

A retrained model changes the vector representation even though its output dimension stays 1536; the corpus must be migrated. Which mechanism addresses the requirement?

  1. Regenerate query embeddings only
  2. Monitor recall against exact baseline
  3. Rebuild embeddings after model change
  4. Matching vector dimension

Explanation

Correct Answer

C:

Re-embedding source content into a consistent model space is necessary because equal dimension does not imply semantic compatibility.

Incorrect Answers

A:

Re-embedding incoming questions alone leaves the stored corpus in an incompatible embedding space after the document model changes.

B:

Exact-neighbor evaluation provides a reference for estimating recall lost by approximate-index strategies; it addresses a different step; the question calls for Rebuild embeddings after model change.

D:

Compatible vector dimensions are required for distance operations; embedding spaces also need consistent model semantics; the required capability here is Rebuild embeddings after model change, which this operation does not supply.

Question 189

A customer-support table contains internal API keys unrelated to semantic retrieval and they must never be sent to an external embedding endpoint. Which option satisfies the desired behavior most directly?

  1. Preserve source identifiers separately
  2. Exclude secret columns
  3. Normalize noisy boilerplate
  4. Title plus body

Explanation

Correct Answer

B:

Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features.

Incorrect Answers

A:

Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval; the condition in the stem is resolved by Exclude secret columns, not by this feature alone.

C:

Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents; that function is not interchangeable with Exclude secret columns for the requested result.

D:

Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail; for the stated conditions, the deciding feature is Exclude secret columns rather than this behavior.

Question 190

Analysts need to query data held in an external source through a relational definition without initially loading all source rows locally. What is the best primary mechanism to use?

  1. Graph node and edge tables
  2. External table
  3. Updatable ledger table
  4. System-versioned temporal table

Explanation

Correct Answer

B:

An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows.

Incorrect Answers

A:

SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records; this differs from the External table requirement described in the scenario.

C:

An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions; the alternative cannot substitute for External table under the given constraints.

D:

Temporal tables maintain current and history rows with system-maintained validity periods, allowing FOR SYSTEM_TIME point-in-time queries; the condition in the stem is resolved by External table, not by this feature alone.

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